Summarizing Alphanumeric Columns

How to:

Reference:

By default, subtotals (using the SUBTOTAL and SUB-TOTAL commands) and recalculations (using the RECOMPUTE and SUMMARIZE commands) only display values for numeric report columns. However, you can include alphanumeric columns on these summary lines by either setting SUMMARYLINES=NEW and specifying the columns you want to display on the summary lines or by using the asterisk wildcard character to display all fields on the summary lines.

The alphanumeric value displayed on a SUBTOTAL or SUB-TOTAL line is either the first or last alphanumeric value within the sort group, depending on the value of the SUMPREFIX parameter. On a RECOMPUTE or SUMMARIZE line, alphanumeric values are recalculated using the summary values for that line.


Top of page

x
Syntax: How to Include All Columns on Summary Lines
ON sortfield summarycommand *

where:

sortfield
Is the sort field for which a change in value triggers the summary line.
summarycommand
Is SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE.
*
Indicates that all fields, numeric and alphanumeric, should be included on the summary lines. This is required if you want to display alphanumeric columns on summary lines when SET SUMMARYLINES is set to OLD. For other values of SUMMARYLINES, you can either use the asterisk to display all columns or reference the specific columns you want to display.


Example: Including Alphanumeric Fields on Summary Lines

The following request against the GGSALES data source computes the alphanumeric equivalents of the DOLLARS and UNITS fields, creates an alphanumeric version of the formula for the ratio between DOLLARS and UNITS, and computes the numeric ratio between DOLLARS and UNITS. The RECOMPUTE * command recomputes all values on a change of value for the state sort field:

SET SUMPREFIX=FST
TABLE FILE GGSALES                                         
SUM PRODUCT DOLLARS/I8M AS 'Dollars' IN 22 UNITS AS 'Units'
COMPUTE Formula/A19 = EDIT(DOLLARS)|'/'|EDIT(UNITS)|'=';   
COMPUTE Ratio/F8    = DOLLARS/UNITS;                       
BY ST                                                      
BY CATEGORY NOPRINT                                        
WHERE ST EQ 'CA' OR 'IL'                                   
ON ST RECOMPUTE *                                          
ON TABLE SET PAGE NOPAGE                                   
END

On the output, the alphanumeric formula is recomputed using the summed numeric fields. However, the product value is taken from the first product within each sort value, as that field is not recomputed and SUMPREFIX=FST by default:

State  Product           Dollars     Units  Formula                 Ratio
-----  -------           -------     -----  -------                 -----
CA     Capuccino      $2,957,852    237246  02957852/00237246=         12
       Biscotti       $2,770,508    222844  02770508/00222844=         12
       Coffee Grinder $1,935,863    152276  01935863/00152276=         13
                                                                         
*TOTAL CA                                                                
       Capuccino      $7,664,223    612366  07664223/00612366=         13
                                                                         
IL     Espresso       $1,398,779    109581  01398779/00109581=         13
       Biscotti       $1,561,904    120976  01561904/00120976=         13
       Coffee Grinder $1,050,243     83541  01050243/00083541=         13
                                                                         
*TOTAL IL                                                                
       Espresso       $4,010,926    314098  04010926/00314098=         13
                                                                         
                                                                         
TOTAL  Capuccino     $11,675,149    926464  11675149/00926464=         13

Note that if the SUBTOTAL summary command had been used, the formula would not have been recomputed and would have displayed the values from the first line within each sort group.


Top of page

x
Reference: Usage Notes for Summarizing Alphanumeric Columns

WebFOCUS