How to: |
You can use the SUMMARIZE and RECOMPUTE commands instead of SUB-TOTAL and SUBTOTAL to recalculate the result of a COMPUTE command. SUMMARIZE is similar to SUB-TOTAL in that it recomputes values at every sort break. RECOMPUTE is similar to SUBTOTAL in that it recalculates only at the specified sort break.
SUMMARIZE recomputes grand totals for the entire report. If you wish to suppress grand totals, you can include the NOTOTAL command in your request. See Suppressing Grand Totals.
{BY|ON} fieldname {SUMMARIZE|RECOMPUTE} [MULTILINES] [field1 [AND] field2...] [AS 'text'][WHEN expression;]
where:
You can also suppress grand totals using the NOTOTAL command, as described in Suppressing Grand Totals.
You can use the asterisk (*) wildcard character instead of a field list to indicate that all fields, numeric and alphanumeric, should be included on the summary lines. You can either use the asterisk to display all columns or reference the specific columns, numeric and alphanumeric, you want to display.
You may also generate subtotals for the recalculated values with the ON TABLE command. Use the following syntax:
ON TABLE SUMMARIZE
The following request illustrates the use of SUMMARIZE to recalculate DG_RATIO at the specified sort break, DEPARTMENT, and for the higher-level sort break, PAY_DATE:
TABLE FILE EMPLOYEE SUM GROSS DED_AMT AND COMPUTE DG_RATIO/F4.2=DED_AMT/GROSS; BY HIGHEST PAY_DATE BY DEPARTMENT BY BANK_ACCT WHERE BANK_ACCT NE 0 ON DEPARTMENT SUMMARIZE END
The first portion of the output is:
PAY_DATE DEPARTMENT BANK_ACCT GROSS DED_AMT DG_RATIO -------- ---------- --------- ----- ------- -------- 82/08/31 MIS 40950036 $1,540.00 $725.34 .47 122850108 $1,815.00 $1,261.40 .69 163800144 $2,255.00 $1,668.69 .74 *TOTAL DEPARTMENT MIS $5,610.00 $3,655.43 .65 PRODUCTION 160633 $2,475.00 $1,427.24 .58 136500120 $1,342.00 $522.28 .39 819000702 $2,238.50 $1,746.03 .78 *TOTAL DEPARTMENT PRODUCTION $6,055.50 $3,695.55 .61 *TOTAL PAY_DATE 82/08/31 $11,665.50 $7,350.98 .63
The last portion of the output is:
PAY_DATE DEPARTMENT BANK_ACCT GROSS DED_AMT DG_RATIO -------- ---------- --------- ----- ------- -------- 82/01/29 PRODUCTION 819000702 $2,035.00 $1,241.33 .61 *TOTAL DEPARTMENT PRODUCTION $2,035.00 $1,241.33 .61 *TOTAL PAY_DATE 82/01/29 $4,182.75 $2,648.12 .63 81/12/31 MIS 163800144 $2,147.75 $1,406.79 .66 *TOTAL DEPARTMENT MIS $2,147.75 $1,406.79 .66 *TOTAL PAY_DATE 81/12/31 $2,147.75 $1,406.79 .66 81/11/30 MIS 163800144 $2,147.75 $1,406.79 .66 *TOTAL DEPARTMENT MIS $2,147.75 $1,406.79 .66 *TOTAL PAY_DATE 81/11/30 $2,147.75 $1,406.79 .66 TOTAL $65,887.25 $41,521.18 .63
Tip: If you use SUB-TOTAL or SUBTOTAL rather than SUMMARIZE, the values of DG_RATIO are added.
The following request illustrates the use of RECOMPUTE to recalculate DG_RATIO only at the specified sort break, DEPARTMENT.
TABLE FILE EMPLOYEE SUM GROSS DED_AMT AND COMPUTE DG_RATIO/F4.2=DED_AMT/GROSS; BY HIGHEST PAY_DATE BY DEPARTMENT BY BANK_ACCT WHERE BANK_ACCT NE 0 ON DEPARTMENT RECOMPUTE END
The first portion of the output is:
PAY_DATE DEPARTMENT BANK_ACCT GROSS DED_AMT DG_RATIO -------- ---------- --------- ----- ------- -------- 82/08/31 MIS 40950036 $1,540.00 $725.34 .47 122850108 $1,815.00 $1,261.40 .69 163800144 $2,255.00 $1,668.69 .74 *TOTAL DEPARTMENT MIS $5,610.00 $3,655.43 .65 PRODUCTION 160633 $2,475.00 $1,427.24 .58 136500120 $1,342.00 $522.28 .39 819000702 $2,238.50 $1,746.03 .78 *TOTAL DEPARTMENT PRODUCTION $6,055.50 $3,695.55 .61 82/07/30 MIS 40950036 $1,540.00 $725.34 .47 122850108 $1,815.00 $1,261.40 .69
The last portion of the output is:
PAY_DATE DEPARTMENT BANK_ACCT GROSS DED_AMT DG_RATIO -------- ---------- --------- ----- ------- -------- 82/01/29 MIS 163800144 $2,147.75 $1,406.79 .66 *TOTAL DEPARTMENT MIS $2,147.75 $1,406.79 .66 PRODUCTION 819000702 $2,035.00 $1,241.33 .61 *TOTAL DEPARTMENT PRODUCTION $2,035.00 $1,241.33 .61 81/12/31 MIS 163800144 $2,147.75 $1,406.79 .66 *TOTAL DEPARTMENT MIS $2,147.75 $1,406.79 .66 81/11/30 MIS 163800144 $2,147.75 $1,406.79 .66 *TOTAL DEPARTMENT MIS $2,147.75 $1,406.79 .66 TOTAL $65,887.25 $41,521.18 .63
Information Builders |