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.

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.