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:
Must be the name of a field in a sort phrase. A BY phrase can include a summary command. The number of fields to summarize multiplied by the number of levels of summary commands counts in the number of display fields permitted for the request. For details on determining the maximum number of display fields that can be used in a request, see Displaying Report Data.
Recomputes values at every sort break.
Recalculates values only at the specified sort break.
Suppresses the printing of a subtotal line for every sort break that has only one detail line, since the subtotal value is equal to this one value. Note that MULTI-LINES is a synonym for MULTILINES. MULTILINES is not supported with horizontal (ACROSS) sort fields.
You can also suppress grand totals using the NOTOTAL command, as described in Suppressing Grand Totals.
Enables you to specify a different label. For related information, see .
Denotes a list of specific fields to be subtotaled after the RECOMPUTE or SUMMARIZE. This list overrides the default, which includes all numeric display fields.
Specifies the conditional display of subtotals based on a Boolean expression. You must end the expression with a semicolon.
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 |