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


Top of page

x
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 acrossfield						sumoption [field1						field2 ... fieldn]

or

ACROSS acrossfield					
ON acrossfield						sumoption [field1						field2 ... 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.
field1 field2 ... fieldn

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


Top of page

x
Reference: Usage Notes for Summaries on ACROSS Fields


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 HTMLEND

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' UNITS AS 'Units'
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:

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:

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