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