INFORMIX SOFTWARE, INC. SmartWareII Technical Support Bulletin 026 Subject: Predicting Breakpoints in Database Reports using User-defined Functions in SmartWareII Date: February 22, 1990 With SmartWareII's ability to place calculations in the Database Report Generator TABLE TITLES and BREAKPOINT result line label, there is a need to determine table report breakpoints through the use of user-defined functions. This is accomplished using this Technical Support Bulletin. This document can be used along with Technical Support Bulletin 18 for generating Database Reports using user-defined functions. User-defined functions are developed with SmartWareII's Project Development Language. The user-defined functions are made available using the REMEMBER LOAD command. public clear_grand(), fetch(1), new_break(1) public $break_field, $break_point, counter, page_number function clear_grand() counter = 0 return blank end function function fetch($sent_field) $break_field = indirect($sent_field) return blank end function function new_break($send_break) $break_point = indirect($send_break) if $break_field <> $break_point counter = counter + 1 page_number = str(counter) counter = 0 return "Page"&page_number else counter = counter + 1 page_number = str(counter) return "Page"&page_number end if end function The function clear_totals() serves as a way of clearing the variables back to a zero value. This function should be placed in a defined GRAND-TOTAL in the Table. By placing the function in the GRAND-TOTAL section, the Report routine evaluates the function at the beginning of the report and clears counter. This is accomplished by placing the function in the Result Line Label, where the word GRAND TOTAL normally appears. After clearing the variables, the functions can be used for another Report routine. Any SmartWare or user defined function can be placed in the result line label of a BREAKPOINT or GRAND-TOTAL. This brings up an interesting point. The Report Generator allows for calculations to be placed in a Grand-Total or Breakpoint result line label. These calculations are evaluated at the beginning of the current action. The results are then printed at the end of the current action. The results are based on the first record of the action and not all records in the break. Fortunately, Table Titles allows for calculations to be placed on both the Headings and Footings. If the calculation is placed in the Footing lines by using an "=" sign as the first character, it will be evaluated at the end of each page of the report. This is the theory that operates Technical Support Bulletin 18. Technical Support Bulletin 18 develops user-defined functions to provide for running and cumulative totals. This Technical Support Bulletin develops functions that allow for determining a new breakpoint value. This then allows the user to develop function libraries that provide results based on the breakpoint. These calculations use the ability that Table Titles allow for calculations. The function fetch(1) should be defined as a calculated column and should pass to the function another field that is present in the report. The function has been prototyped to receive one field, or value by declaring in the PUBLIC statement one argument. The field passed to fetch(1) should be the breakpoint field. The calculation should be developed using the following syntax fetch("[breakpoint_field]") Notice that the calculation sends the field title as text to the function. This allows the function to determine the current value of the field. The indirect() function in the fetch(1) function will calculate the current break field value. The function new_break(1) should be placed in the TABLE TITLES section of the Report. While the desirable place would be in a FORM CALCULATION field or the result line label of the breakpoint, it is not evaluated in the desired order. The FORM portion of a FORM-TABLE or a FORM-TABLE-FORM is evaluated on the first record and then the table records are evaluated and processed. The final page consisting of the FORM and TABLE is then printed. The calculation should be developed using the following syntax Footings: Line 1: =new_break("[breakpoint_field]") In the user-defined example above, page numbers are returned based on the breakpoint value. Each breakpoint has its own page numbering scheme. New_break(1) returns an alphanumeric result that includes the static text Page and a variable page number from the variables counter and page_number. The function new_break(1) is evaluated at the end of each physical page that the table produces. If a new break record is accessed when this function is called, the variable break_point will reflect the new break value. The function new_break(1) can be combined with other user-defined functions to calculate information based on the break information. The IF statement determines when a new break is taking effect. The THEN condition, or when the condition is TRUE, indicates a new break condition has occurred. Any function library can be called or combined with the RETURN command within the THEN part of the IF statement. The ELSE part of the IF statement contains the condition when the breakpoint has not changed. At this point, the report is still under the current break value. Any function library can be called or combined with the RETURN command within the ELSE part of the IF statement. Informix SmartWareII Technical Support hopes that this Technical Support Bulletin is useful to SmartWareII end-users. While these supplied functions are simple, they represent a starting place for developing programming structures and techniques for applying user developed applications in SmartWare II commands.