Try OpenEdge Now
skip to main content
Programming Interfaces
Input/Output Processes : Creating Reports : Generating reports with control breaks and aggregates
 

Generating reports with control breaks and aggregates

You can sort data using the BY option of the record phrase. For example, this block header in a report procedure sorts the Customer records by the SalesRep code:
FOR EACH Customer NO-LOCK BY Customer.SalesRep:
A control break separates sorted data into meaningful groups. Breaking sorted data by SalesRep separates each sales rep's customers into a separate break group. Break groups allow you to calculate summary data on subsets of data. For example, you could total the number of customers that each sales rep serves.
The BREAK option of the record phrase sets up your data for control breaks, as shown in this block header statement:
FOR EACH Customer NO-LOCK BREAK BY Customer.SalesRep:
Once you've set up the control break, you can use an aggregate phrase on an output statement item to calculate summary information. This is the syntax of an aggregate phrase:

Syntax

[ aggregate-option ] ... [ BY break-group ] ...
The following table lists the aggregate options.
Table 44. Aggregate options
Aggregate option
Description
AVERAGE
Calculates the average of all the expression's values, and calculates the overall average for the break groups
SUB-AVERAGE
Calculates the average of all the expression's values, but does not calculate the overall average
COUNT
Counts the number of items in the break group, and calculates the overall count of the break groups
SUB-COUNT
Counts the number of items in the break group, but does not calculate the overall count
MAXIMUM
Finds the maximum value of the expression in the break group, and finds the overall maximum of the break groups
SUB-MAXIMUM
Finds the maximum value of the expression in the break group, but does not find the overall maximum
MINIMUM
Finds the minimum value of the expression in the break group, and finds the overall minimum of the break groups
SUB-MINIMUM
Finds the minimum value of the expression in the break group, but does not find the overall minimum
TOTAL
Calculates the total of all the expression's values, and calculates the overall total for the break group.
SUB-TOTAL
Calculates the total of all the expression's values, but does not calculate the overall total for the break groups
In the example below, SalesRep is the control break, Balance is the expression, and the aggregate phrase with the TOTAL option appears in parenthesis immediately after the expression in the DISPLAY statement:
FOR EACH Customer NO-LOCK
  BREAK BY Customer.SalesRep:
DISPLAY Customer.Balance (TOTAL BY Customer.SalesRep).
END.
The TOTAL aggregate option totals the balances for each break group. Each time the value of SalesRep changes in the sorted data, the AVM creates another break group, and the TOTAL BY aggregate function calculates and outputs the total of customer balances for that sales rep's customers. Finally, at the end of the report, the TOTAL option calculates and outputs the total balances of all the break groups.
To see an example of control breaks and aggregate values:
1. Open i-10-05.p and run it.
2. Click Report. The Report Output dialog box appears:
The members of each break group all have the same SalesRep value. Notice that the report separates each break group with white space and outputs the result of the aggregate phrase options immediately following the break groups.
If you scroll to the bottom of the report, you can see the aggregate values for all the break groups.
3. Click OK, then Exit, and then press SPACEBAR to return to the Procedure Editor.
Here is the code for this procedure:
i-10-05.p
    /* {i-10-in.i} Common Interface Setup Code */

/********** DEFINE TRIGGERS **********/
ON CHOOSE OF b-rep DO:
      OUTPUT TO "tut-temp.txt".
/*1*/ FOR EACH Customer NO-LOCK
        BREAK BY Customer.SalesRep WITH STREAM-IO:
        DISPLAY Customer.SalesRep Customer.Name
/*2*/     Customer.Balance (COUNT TOTAL AVERAGE BY Customer.SalesRep).
      END.
      OUTPUT CLOSE.

      ASSIGN
        Rep-Editor:READ-ONLY IN FRAME dialog1 = TRUE
        Rep-Editor:SENSITIVE IN FRAME Dialog1 = TRUE
        FRAME dialog1:TITLE                   = "Report Output"
        lStat = Rep-Editor:READ-FILE("tut-temp.txt") IN FRAME Dialog1.

      IF lStat THEN DO:
        ENABLE Rep-Editor b-ok WITH FRAME Dialog1.
        WAIT-FOR GO OF FRAME Dialog1.
        HIDE FRAME Dialog1.
      END.
END. /* ON CHOOSE OF b-rep */

/********** MAIN LOGIC **********/
ENABLE ALL WITH FRAME Frame1.
WAIT-FOR CHOOSE OF b-exit.
The BREAK option of the record phrase sets up the control break.
The aggregate phrase after the Balance expression contains three aggregate options.