Combinations of Summary Commands

Reference:

You can specify a different summary operation for each sort break (BY or ACROSS field).

If you have multiple summary commands for the same sort field, the following message displays and the last summary command specified in the request is used: 

(FOC36359)  MORE THAN 1 SUBTOTAL/SUB-TOTAL/RECOMPUTE/SUMMARIZE
There is more than one SUBTOTAL/SUB-TOTAL/RECOMPUTE/SUMMARIZE 
on the same key field which is not allowed. The last one specified will 
override the rest.

SUMMARIZE and SUB-TOTAL, which propagate their summary operations to higher level sort breaks, skip those fields at higher level sort breaks that have their own summary commands. The propagation of summary operations depends on whether prefix operator processing is used for summary lines. If prefix operators are:

Prefix operators on summary lines result in the same values whether the command is RECOMPUTE/SUMMARIZE or SUBTOTAL/SUB-TOTAL. For a computed field, the prefix operator is not applied, and the value is recalculated using the expression in the COMPUTE command and the values from the summary line.

When you use different summary commands for different sort fields, the default grand total row inherits the summary command associated with the first sort field in the request. You can change the operation performed at the grand total level by using the ON TABLE phrase to specify a specific summary command.

Note: The grand total is considered the highest sort level. Therefore, although you can use the SUMMARIZE or SUB-TOTAL command at the grand total level, these commands apply only to the grand total and are not propagated to any other line on the report. On the grand total level SUMMARIZE operates as a RECOMPUTE command, and SUB-TOTAL operates as a SUBTOTAL command.

Example: Using SUBTOTAL and RECOMPUTE in a Request

In the following request, the first sort field specified is COPIES, which is associated with the RECOMPUTE command. Therefore, on the grand total line, the value of RATIO is correctly recomputed and the values of LISTPR and WHOLESALEPR are summed (because this is the default operation when the field is not calculated by a COMPUTE command). 

TABLE FILE MOVIES
PRINT DIRECTOR LISTPR WHOLESALEPR
COMPUTE RATIO = LISTPR/WHOLESALEPR;
BY COPIES
BY RATING
WHERE COPIES LT 3
WHERE DIRECTOR EQ 'DISNEY W.' OR 'HITCHCOCK A.'
ON COPIES RECOMPUTE AS '*REC: '
ON RATING SUBTOTAL AS '*SUB:  '
END

The output is:

COPIES  RATING  DIRECTOR           LISTPR  WHOLESALEPR           RATIO
------  ------  --------           ------  -----------           -----
     1  NR      DISNEY W.           29.95        15.99            1.87

*SUB:   NR                          29.95        15.99            1.87
*REC:    1                          29.95        15.99            1.87

     2  NR      HITCHCOCK A.        19.98         9.00            2.22

*SUB:   NR                          19.98         9.00            2.22

        PG      HITCHCOCK A.        19.98         9.00            2.22
                HITCHCOCK A.        19.98         9.00            2.22

*SUB:   PG                          39.96        18.00            4.44
     2  PG13    HITCHCOCK A.        19.98         9.00            2.22

*SUB:   PG13                        19.98         9.00            2.22

        R       HITCHCOCK A.        19.98         9.00            2.22

*SUB:   R                           19.98         9.00            2.22
*REC:    2                          99.90        45.00            2.22


TOTAL                              129.85        60.99            2.13

If you reverse the BY fields, the grand total line sums the RATIO values as well as the LISTPR and WHOLESALEPR values because the SUBTOTAL command controls the grand total line: 

TOTAL                              129.85        60.99           12.97

You can change the operation performed at the grand total level by adding the following command to the request: 

ON TABLE RECOMPUTE

The grand total line then displays the recomputed values: 

TOTAL                              129.85        60.99            2.13

Example: Using SUB-TOTAL With Multiple Summary Commands

In the following request, the SUB-TOTAL command propagates its operation to the DIRECTOR sort field (see the total line for HITCHCOCK, on which the RATIO values are subtotaled, not recomputed).

SUB-TOTAL is not propagated to the RATING sort field which has its own RECOMPUTE command, and for this sort field the RATIO value is recomputed. The grand total line is recomputed because RECOMPUTE is performed on a higher level sort field than SUB-TOTAL. 

TABLE FILE MOVIES
PRINT LISTPR WHOLESALEPR
COMPUTE RATIO = LISTPR/WHOLESALEPR;
BY DIRECTOR
BY RATING
BY COPIES
WHERE COPIES LT 3
WHERE DIRECTOR EQ 'HITCHCOCK A.'
ON COPIES SUB-TOTAL AS '*SUB: '
ON RATING RECOMPUTE AS '*REC:  '
END

The output is:

DIRECTOR           RATING  COPIES  LISTPR  WHOLESALEPR           RATIO
--------           ------  ------  ------  -----------           -----
HITCHCOCK A.       NR           2   19.98         9.00            2.22

*SUB:    2                          19.98         9.00            2.22
*REC:   NR                          19.98         9.00            2.22

                   PG           2   19.98         9.00            2.22
                                    19.98         9.00            2.22

*SUB:    2                          39.96        18.00            4.44
*REC:   PG                          39.96        18.00            2.22

                   PG13         2   19.98         9.00            2.22
*SUB:    2                          19.98         9.00            2.2
*REC:   PG13                        19.98         9.00            2.2

HITCHCOCK A.       R            2   19.98         9.00            2.2

*SUB:    2                          19.98         9.00            2.2
*REC:   R                           19.98         9.00            2.2
*TOTAL DIRECTOR HITCHCOCK A.        99.90        45.00           11.1


TOTAL                               99.90        45.00            2.2

Example: Using Multiple Summary Commands With Prefix Operators

The following request prints the average value of LISTPR and the recomputed value of RATIO on the lines associated with sort field RATING. The SUB-TOTAL command associated with sort field COPIES is propagated to all fields on the DIRECTOR sort field lines and to the WHOLESALEPR and RATIO1 columns associated with the RATING sort field. The grand total line is suppressed for this request. 

TABLE FILE MOVIES
PRINT LISTPR WHOLESALEPR
COMPUTE RATIO/D6.2 = LISTPR/WHOLESALEPR;
COMPUTE RATIO1/D6.2 = LISTPR/WHOLESALEPR;
BY DIRECTOR
BY RATING
BY COPIES
WHERE COPIES LT 3
  WHERE DIRECTOR EQ 'KAZAN E.'
  ON RATING  RECOMPUTE  AVE. LISTPR  RATIO AS '*REC:  '
  ON COPIES  SUB-TOTAL                     AS '*SUB:  '
  ON TABLE NOTOTAL
END

On the output:

The output is:

DIRECTOR           RATING  COPIES  LISTPR  WHOLESALEPR   RATIO  RATIO1
--------           ------  ------  ------  -----------   -----  ------
KAZAN E.           NR           1   24.98        14.99    1.67    1.67

*SUB:    1                          24.98        14.99    1.67    1.67

                                2   19.95         9.99    2.00    2.00

*SUB:    2                          19.95         9.99    2.00    2.00
*REC:   NR                          22.46        24.98     .90    3.66
*TOTAL DIRECTOR KAZAN E.            44.93        24.98    3.66    3.66

Example: Propagation of Summary Commands With Field Lists

In the following request, the RECOMPUTE command has a field list. 

TABLE FILE MOVIES
PRINT LISTPR WHOLESALEPR
COMPUTE RATIO/D6.2 = LISTPR/WHOLESALEPR;
COMPUTE RATIO1/D6.2 = LISTPR/WHOLESALEPR;
BY DIRECTOR
BY RATING
BY COPIES
WHERE COPIES LT 3
  WHERE DIRECTOR EQ 'KAZAN E.'
  ON RATING RECOMPUTE LISTPR RATIO AS '*REC:  '
  ON COPIES SUB-TOTAL AS '*SUB:  '
END

SUB-TOTAL propagates to all of the columns that would otherwise be unpopulated. The grand total line inherits the RECOMPUTE command for the fields listed in its field list, and the SUB-TOTAL command propagates to the other columns: 

DIRECTOR           RATING  COPIES  LISTPR  WHOLESALEPR   RATIO  RATIO1
--------           ------  ------  ------  -----------   -----  ------
KAZAN E.           NR           1   24.98        14.99    1.67    1.67

*SUB:     1                         24.98        14.99    1.67    1.67

                                2   19.95         9.99    2.00    2.00

*SUB:     2                         19.95         9.99    2.00    2.00
*REC:   NR                          44.93        24.98    1.80    3.66
*TOTAL DIRECTOR KAZAN E.            44.93        24.98    3.66    3.66


TOTAL                               44.93        24.98    1.80    3.66

Reference: Usage Notes for Combinations of Summary Commands

  • SET SUMMARYLINES=EXPLICIT affects propagation of summary commands to the grand total line by making it consistent with the behavior for any sort break. Therefore, with this setting in effect, SUB-TOTAL and SUMMARIZE propagate to the grand total line but SUBTOTAL and RECOMPUTE do not.