Recalculating Values for Subtotal Rows

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.

Syntax: How to Subtotal Calculated Values 

{BY|ON} fieldname {SUMMARIZE|RECOMPUTE} [MULTILINES]
      [field1 [AND] field2...] [AS 'text'][WHEN expression;]

where:

fieldname
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 for the request. For details on determining the maximum number of display fields that can be used in a request, see Displaying Report Data.
SUMMARIZE
Recomputes values at every sort break.
RECOMPUTE
Recalculates values only at the specified sort break.
MULTILINES
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.

AS 'text'
Enables you to specify a different label. For related information, see Using Headings, Footings, Titles, and Labels.
field1, field2, ...
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.

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.

WHEN expression
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

Example: Using 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.

Example: Using RECOMPUTE

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