Producing Summary Columns for Horizontal Sort Fields

How to:

Reference:

The summary commands SUBTOTAL, SUB-TOTAL, SUMMARIZE, and RECOMPUTE can be used with horizontal sort breaks.

When a request has multiple display fields and an ACROSS sort field, the report output has multiple columns under each ACROSS value. If you want to apply a summary field to some of the columns for each ACROSS value, but not others, you can specify the field names you want summarized. This technique is most useful for report requests that use the OVER phrase to place the fields on separate rows

Syntax: How to Produce a Summary Operation on a Horizontal Sort Field 

{ACROSS|ON} acrossfield [AS 'text1'] sumoption [AS 'text2']
             [COLUMNS c1 [AND c2 ...]]

or

ACROSS acrossfieldsumoption [field1field2 ... fieldn]

or

ACROSS acrossfield
ON acrossfieldsumoption [field1field2 ... fieldn]

where:

acrossfield
Is the ACROSS field whose for which you want to generate the summary option. The end of the values for the ACROSS field triggers the summary operation.
sumoption
Can be one of the following: SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE.
'text1'
Is the column heading to use for the break field on the report output.
'text2'
Is the text that prints on the top of the summary column.
COLUMNSc1, c2 ...
Lists the specific ACROSS values that you want to display on the report output in the order in which you want them. This list of values cannot be specified in an ON phrase. If it is specified in an ACROSS phrase, it must be the last option specified in the ACROSS phrase.
field1field2 ... fieldn

Are the fields that will have the summary command applied. If no fields are listed, all fields will be summarized.

Reference: Usage Notes for Summaries on ACROSS Fields

  • SUMMARIZE and SUB-TOTAL operate on the ACROSS field for which they are specified and for all higher level ACROSS fields. They do not operate on BY fields. SUBTOTAL and RECOMPUTE operate only on the ACROSS field for which they are specified. However, the summary is not produced until the higher level ACROSS field changes value.
  • SUMMARIZE and SUB-TOTAL commands specified for a BY field operate on that BY and all higher level BY fields. They do not operate on ACROSS fields.
  • ROW-TOTAL, ACROSS-TOTAL, SUBTOTAL, and SUB-TOTAL sum the values in the columns. Unlike SUMMARIZE and RECOMPUTE, they do not reapply calculations other than sums.
  • Summary commands specified in an ON TABLE phrase operate on columns, not rows.
  • With ACROSS, summary columns only display at the end of the ACROSS group (when the higher-level ACROSS field changes value).
  • Different operations from two ON phrases for the same sort break display in the same summary column, and allow a mixture of operations on summary columns.
  • If the same field is referenced in more than one ON phrase for the same sort break, the last summary command specified is applied.
  • You can specify a different summary operation for each sort break.
  • The SUMMARYLINES parameter does not affect processing for ACROSS fields.
  • When used with OVERs, the rows containing fields not to be summarized will be blank.
  • Prefix operators are supported on summary lines:
    • The following prefix operators are supported for numeric fields: ASQ., AVE., CNT., FST., LST., MAX., MIN., SUM.
    • Prefix operators PCT., RPCT., AND TOT. are not supported.
    • Double prefix operators (such as PCT.CNT.) are not supported.
    • The SUM. prefix operator produces the same summary values as a summary phrase with no prefix operator.
    • SUMMARIZE and RECOMPUTE apply the calculations defined in the associated COMPUTE command to the summary values. Therefore, in order to perform the necessary calculations, the SUMMARIZE or RECOMPUTE command must specify all of the fields referenced in the COMPUTE command.
    • If the same field has summary operations with different prefix operators at each level, the appropriate calculation is done at each level for the prefix operator specified.
    • SUB-TOTAL and SUMMARIZE propagate their operations to all higher-level sort fields. If a request uses SUB-TOTAL or SUMMARIZE at multiple sort levels, more than one prefix operator may apply to the same field. When a SUB-TOTAL or SUMMARIZE command on a lower-level sort field propagates up to the higher levels, it applies its prefix operators only to those fields that did not already have a prefix operator specified at the higher level. For any field that had a prefix operator specified at a higher level, the original prefix operator is applied at the level at which it was first specified.
    • 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.
    • If an ACROSS field has an ACROSS-TOTAL phrase and a summary command with a prefix operator, the prefix operator is applied, not the ACROSS-TOTAL.

Example: Using Summary Commands With ACROSS

The following request sums units and dollars and calculates the unit cost by product and across region and month. The ACROSS MNTH RECOMPUTE command creates totals of units and dollars, and recomputes the calculated value for the selected months within regions. The ACROSS REGION RECOMPUTE command does the same for the selected regions. The ON TABLE SUMMARIZE command creates summary rows. It has no effect on columns: 

DEFINE FILE GGSALES
MNTH/MTr   = DATE;
END
TABLE FILE GGSALES
SUM
 UNITS/I5 AS 'UNITS'                   OVER
 DOLLARS/I6 AS 'DOLLARS'               OVER
 COMPUTE DOLLPER/I6 = DOLLARS/UNITS; AS 'UNIT COST'
BY PRODUCT
ACROSS REGION RECOMPUTE AS 'Region Sum' COLUMNS 'Northeast' AND 'West'
ACROSS MNTH   RECOMPUTE AS 'Month Sum' COLUMNS 'November' AND 'December'
WHERE DATE FROM '19971101' TO '19971231';
WHERE PRODUCT EQ 'Capuccino' OR 'Espresso';
ON TABLE SUMMARIZE AS 'Grand Total'
ON TABLE HOLD FORMAT HTML
END

The output is:

Example: Subtotaling One Field Within an ACROSS Group

The following request against the GGSALES data source sums the DOLLARS and UNITS fields by CATEGORY and across REGION, but subtotals only the UNITS field. 

TABLE FILE GGSALES
SUM DOLLARS AS 'Dollars' OVER
UNITS AS 'Units'
  BY CATEGORY
  ACROSS REGION SUBTOTAL UNITS
WHERE REGION EQ 'Midwest' OR 'West'
ON TABLE SET PAGE NOPAGE
END

The output shows that only the rows with the UNITS values are subtotaled. 

                      Region
                      Midwest      West         TOTAL
Category
-------------------------------------------------------------
Coffee       Dollars   4178513      4473517
             Units      332777       356763       689540
Food         Dollars   4338271      4202337
             Units      341414       340234       681648
Gifts        Dollars   2883881      2977092
             Units      230854       235042       465896

Example: Summarizing a Calculated Value in an ACROSS Group

The following request against the GGSALES data source sums the DOLLARS and UNITS fields and calculates DOLLARS PER UNIT across REGION. The request also has a higher-level ACROSS field, CATEGORY, so the SUMMARIZE command propagates to both ACROSS fields. 

SET BYPANEL = ON
TABLE FILE GGSALES
SUM DOLLARS AS 'Dollars' OVER
UNITS AS 'Units'         OVER
AND COMPUTE DPERU/D9.2 = DOLLARS/UNITS;
  ACROSS CATEGORY
  ACROSS REGION
  ON REGION SUMMARIZE DPERU
  WHERE REGION EQ 'Midwest' OR 'West'
  WHERE CATEGORY EQ 'Food' OR 'Gifts'
  ON TABLE HOLD FORMAT PDF
END

The first panel of output shows:

  • The values of DOLLARS, UNITS, and DPERU for the Midwest and West regions under the Food category.
  • The summary column, which has a value just for the DPERU row. Note that for ACROSS, the summary column for REGION appears only after the higher-level ACROSS field, CATEGORY, changes value.
  • The values of DOLLARS, UNITS, and DPERU for the Midwest and West regions under the Gifts category. 
PAGE   1.1


         Category
         Food                                   Gifts
         Region
         Midwest      West         TOTAL        Midwest      West
-------------------------------------------------------------------------
Dollars      4338271      4202337                   2883881      2977092
Units         341414       340234                    230854       235042
DPERU          12.71        12.35        12.53        12.49        12.67

The second panel has the total column for the Gifts category and the grand total column. Each of those only has a value in the DPERU row. 

 PAGE   1.2


         Category
                       TOTAL
         Region
          TOTAL
 ----------------------------------
 Dollars
 Units
 DPERU          12.58        12.55

Example: Using Prefix Operators in a Summary Command With ACROSS

The following request against the GGSALES data source sums the DOLLARS and UNITS fields ACROSS CATEGORY and ACROSS REGION, with a SUMMARIZE command on the REGION field. The request also has a higher-level ACROSS field, CATEGORY, so the SUMMARIZE command propagates to both ACROSS fields. The SUMMARIZE command specifies the AVE. prefix operator for the DOLLARS field. 

SET BYPANEL = ON
TABLE FILE GGSALES
SUM DOLLARS AS 'Dollars' OVER
UNITS AS 'Units'
  ACROSS CATEGORY
  ACROSS REGION
  ON REGION SUMMARIZE AVE. DOLLARS
  WHERE REGION EQ 'Midwest' OR 'West'
  WHERE CATEGORY EQ 'Food' OR 'Gifts'
  ON TABLE HOLD FORMAT PDF
END

The first panel of output shows:

  • The values of DOLLARS and UNITS for the Midwest and West regions under the Food category.
  • The summary column, which has a value just for the DOLLARS row. Note that for ACROSS, the summary column for REGION appears only after the higher-level ACROSS field, CATEGORY, changes value.
  • The values of DOLLARS and UNITS for the Midwest and West regions under the Gifts category. 
PAGE   1.1


         Category
         Food                                   Gifts
         Region
         Midwest      West         TOTAL        Midwest      West
-------------------------------------------------------------------------
Dollars    4338271      4202337      4270304      2883881      2977092
Units       341414       340234                    230854       235042

The second panel has the total column for the Gifts category and the grand total column. Each of those only has a value in the DOLLARS row. 

PAGE   1.2


        Category
                      TOTAL
        Region
         TOTAL
----------------------------------
Dollars    2930486      3600395
Units

Example: Using Combinations of ACROSS Summary Commands

The following request against the GGSALES data source sums the DOLLARS and UNITS fields ACROSS CATEGORY and ACROSS REGION, with a SUMMARIZE command on the REGION field and a SUBTOTAL command on the CATEGORY field. The SUMMARIZE command specifies average DOLLARS and minimum UNITS. The SUBTOTAL command specifies minimum DOLLARS. 

SET BYPANEL = ON
TABLE FILE GGSALES
SUM DOLLARS AS 'Dollars' OVER
UNITS AS 'Units'
  ACROSS CATEGORY
  ACROSS REGION
    ON CATEGORY SUBTOTAL MIN. DOLLARS
    ON REGION SUMMARIZE AVE. DOLLARS MIN. UNITS
  WHERE REGION EQ 'Midwest' OR 'West'
  WHERE CATEGORY EQ 'Food' OR 'Gifts'
  
END

On the output, all of the TOTAL columns have the minimum UNITS. The TOTAL columns associated with the REGION sort field have the average DOLLARS, but the TOTAL column associated with the CATEGORY sort field has the minimum DOLLARS because SUMMARIZE does not change the prefix operator associated with a higher-level sort field. 

PAGE   1.1


         Category
         Food                                   Gifts
         Region
         Midwest      West         TOTAL        Midwest      West
-------------------------------------------------------------------------
Dollars    4338271      4202337      4270304      2883881      2977092
Units       341414       340234       340234       230854       235042
PAGE   1.2


        Category
                      TOTAL
        Region
         TOTAL
----------------------------------
Dollars    2930486      2883881
Units       230854       230854
 Information Builders