Including Section Totals and a Grand Total

Frequently, reports contain detailed information that is broken down into subsections, for which simple column and row totals may not provide adequate summaries. In these instances, it is more useful to look at subtotals for particular sections, and a grand total.

You can add the following commands to your requests to create section subtotals and grand totals:

Each command produces grand totals and/or subtotals by using different information. Subtotals produce totals every time a specified sort field value changes, and are independent of record selection criteria. You can further control when subtotals are produced by specifying WHEN criteria (see Conditionally Displaying Summary Lines and Text). You can control whether subtotals display above or below the data. For information, see Control Placement of Summary Lines. You can also suppress grand totals using the NOTOTAL command. For details, see Suppressing Grand Totals.

By default, a blank line is generated before a subtotal on the report output. You can eliminate these automatic blank lines by issuing the SET DROPBLNKLINE=ON command.

Note: When the request has a PAGE-BREAK command, the GRANDTOTAL is on a page by itself.

You can use prefix operators with SUBTOTAL, SUB-TOTAL, SUMMARIZE, and RECOMPUTE. For details, see Manipulating Summary Values With Prefix Operators. In addition, you can combine different summary operations in a single request. For information, see Combinations of Summary Commands.

Example: Using Section Totals and Grand Totals

The following request illustrates how to create a subtotal every time the department value changes. The grand total is automatically produced when you use the SUBTOTAL command. 

TABLE FILE EMPLOYEE
SUM DED_AMT BY DED_CODE BY DEPARTMENT
BY BANK_ACCT
WHERE BANK_ACCT NE 0
WHERE DED_CODE EQ 'CITY' OR 'FED'
ON DEPARTMENT SUBTOTAL
END

The first and last portions of the output are: 

DED_CODE  DEPARTMENT  BANK_ACCT          DED_AMT
--------  ----------  ---------          -------
CITY      MIS          40950036           $14.00
                      122850108           $31.75
                      163800144           $82.70
                                                
*TOTAL DEPARTMENT MIS                    $128.45
                                                
          PRODUCTION     160633            $7.42
                      136500120           $18.25
                      819000702           $60.20
                                                
*TOTAL DEPARTMENT PRODUCTION              $85.87
                                                
FED       MIS          40950036        $1,190.77
                      122850108        $2,699.80
                      163800144        $7,028.30
                                               
*TOTAL DEPARTMENT MIS                 $10,918.87
                                                
          PRODUCTION     160633          $631.12
                      136500120        $1,552.10
                      819000702        $5,120.04
                                                
*TOTAL DEPARTMENT PRODUCTION           $7,303.26
                                                
                                                
TOTAL                                 $18,436.45