Release 10.1A: OpenEdge Development:
Programming Interfaces

> ## 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 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 7–1 describes the aggregate options.

> Table 7–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.

> | To see an example of control breaks and aggregate values:

> 1. Open `i-10-05.p` and run it.

> 2. Choose Report. The Report Output dialog box appears as shown in the following screen:

> 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.

> 3. Choose 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 FIELDS (Sales-rep Name Balance) 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 © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095