Progress
Language Tutorial
for Windows


Generating Reports with Control Breaks and Aggregates

In "Selecting, Sorting, and Relating Records," you learned to 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 Sales-rep code:

FOR EACH Customer BY Sales-Rep: 

A control break separates sorted data into meaningful groups. Breaking sorted data by Sales-rep 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 BREAK BY Sales-Rep: 

Once you’ve set up the control break, you can use the aggregate phrase on an output statement item to calculate summary information. The diagram below shows the syntax of the aggregate phrase.

SYNTAX
[ aggregate-option ] ... [ BY break-group ] ... 

Table 10–1 describes the aggregate options.

Table 10–1: Aggregate Phrase 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 groups.
SUB-TOTAL
Calculates the total of all the expression’s values, but does not calculate the overall total for the break groups.

For example, in the code below, Sales-rep 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 FIELDS (Balance Sales-Rep) BREAK BY Sales-rep:
  DISPLAY Customer Balance (TOTAL BY Sales-Rep).
END. 

The TOTAL aggregate option totals the balances for each break group. Each time the value of Sales-rep changes in the sorted data, Progress 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.

Follow these steps to demonstrate control breaks and aggregate values:

  1. Open lt-10-05.p and run it.
  2. Choose Report. The report dialog box appears as shown in the following screen:
  3. The members of each break group all have the same Sales-rep 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.

  4. Choose OK, then Exit, and then press SPACEBAR to return to the Procedure Editor.

Here is the code for this procedure:

lt-10-05.p
    {lt-10-in.i} /* Common Interface Setup Code */  
     
    /**********  DEFINE TRIGGERS  **********/
    ON CHOOSE of b-rep
    DO:  
        OUTPUT TO "tut-temp.txt".
/*1*/    FOR EACH Customer BREAK BY Sales-rep WITH STREAM-IO:
            DISPLAY Sales-rep Name 
/*2*/           Balance (COUNT TOTAL AVERAGE BY Sales-rep).
        END.
        OUTPUT CLOSE.
    
        ASSIGN Rep-Editor:READ-ONLY IN FRAME dialog1 = YES
             Rep-Editor:SENSITIVE IN FRAME Dialog1 = YES 
             FRAME dialog1:TITLE = "Report Output"
             Stat = Rep-Editor:READ-FILE("tut-temp.txt") IN FRAME Dialog1.
                
        IF Stat THEN DO:
            ENABLE Rep-Editor b-ok WITH FRAME Dialog1.
            WAIT-FOR GO OF FRAME Dialog1.
            HIDE FRAME Dialog1.
        END.
    END.

    /**********  MAIN LOGIC  **********/
    ENABLE ALL WITH FRAME Frame1.
    WAIT-FOR CHOOSE OF b-exit. 

These notes point out the new features:

  1. The BREAK option of the record phrase sets up the control break.
  2. The aggregate phrase after the Balance expression contains three aggregate options.

Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095