Manipulating Summary Values With Prefix Operators

In this section:

How to:

Reference:

You can use the SUBTOTAL, SUB-TOTAL, RECOMPUTE, and SUMMARIZE commands at the ON TABLE level to specify the type of summary operation to use to produce the grand total line on the report.

In addition, prefix operators can be used with the summary options SUBTOTAL, SUB-TOTAL, RECOMPUTE, and SUMMARIZE at both the sort break and grand total levels. If the same field was aggregated using multiple prefix operators in the SUM command, you can use the prefix operator along with the field name to differentiate between the fields with multiple operators in the summary command.

Prefix operations on summary lines are performed on the retrieved, selected, and summed values that become the detail lines in the report. Unlike field-based prefix operations, they are not performed on each incoming record.

Each type of summary has its own purpose, and handles the prefix operators appropriately for the type of summary information to be displayed. For example, using AVE. at a sort field break produces the average within the sort group.

Alphanumeric fields can also be displayed on summary lines. In order to do this, you must either explicitly list the alphanumeric field name on the summary command, or use the asterisk (*) wildcard to display all fields.

Different operations from two ON phrases for the same sort break display on the same summary line, and allow a mixture of operations on summary lines. The grand total line populates all fields populated by any summary command, even fields that are not specified in the grand total command.

If the same field is referenced in more than one ON phrase for the same sort break, the last function specified is applied.

The following prefix operators are supported for numeric fields:

The following prefix operators are supported for alphanumeric fields:

Syntax: How to Use Prefix Operators With Summary Values

{BY|ON} breakfield [AS 'text1'] sumoption [MULTILINES]
        [pref. ] [*|[field1 [[pref2. ] field2 ...]]]
        [AS 'text2'] [WHEN expression;]

To replace the default grand total, use the following syntax

ON TABLE sumoption [pref. ][field1 [[pref2. ]field2 ...]] [AS 'text2']

where:

breakfield
Is the sort field whose change in value triggers the summary operation. A BY phrase can include a summary command. When the value of the sort field changes, it 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.
MULTILINES
Suppresses the printing of a summary line for every sort break that has only one detail line. Note that MULTILINES suppresses the summary line even if a prefix operator is used to specify a different operation for the summary line. MULTI-LINES is a synonym for MULTILINES. MULTILINES is not supported with horizontal (ACROSS) sort fields.
pref.
Is a prefix operator. When specified without a field list, the prefix operator is applied to every numeric column in the report output and every numeric column is populated with values on the summary row.
*
Includes all display fields on the summary line. If a prefix operator is specified, it is applied to all fields. If the prefix operator is not supported with alphanumeric fields, alphanumeric fields are not included on the summary line.
[field1 [field2 ... fieldn]]
Produces the type of summary specified by sumoption for the listed fields. If no field names are listed, the summary is produced for every numeric column in the report output.
pref. field1 [field2 ... fieldn] [pref2. fieldm ...]
The first prefix operator is applied to field1 through fieldn. The second prefix operator is applied to fieldm. Only the fields specified are populated with values on the summary row. Each prefix operator must be separated by a blank space from the following field name. For example:
'text2'
Is the text that prints on the left of the summary row.
expression
Is an expression that determines whether the summary operation is performed at each break.

Reference: Usage Notes for Summary Prefix Operators

  • COLUMN-TOTAL does not support prefix operators.
  • Prefix operators PCT., RPCT., AND TOT. are not supported.
  • Double prefix operators (such as PCT.CNT.) are not supported.
  • When an ACROSS field is used in the request, the same field name displays over multiple columns (ACROSS groups) in the report output. A prefix operator applied to such a field on a summary line is applied to all of those columns.
  • 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 calculate all of the fields referenced in the COMPUTE command.
  • If the same field is referenced by more than one summary operation with different prefix operators at each level, the default grand total (one produced without an ON TABLE summaryoption command) applies the operation specified by the first operator used in the report request (the left-most sort field in the output).

Example: Using Prefix Operators With SUBTOTAL

The following example uses prefix operators to calculate the:

  • Average list price by rating.
  • Sum copies by category within the rating field.

Notice that the subtotal row for each rating contains a value only in the LISTPR column, and the subtotal row for each category contains a value only in the COPIES column. The grand total line contains values only for the columns that were subtotaled. Note the blank space between each prefix operator and the field name that follows it:

TABLE FILE MOVIES
PRINT COPIES LISTPR WHOLESALEPR TITLE/A23
  BY RATING
  BY CATEGORY
  WHERE CATEGORY EQ 'CHILDREN' OR 'CLASSIC'
  WHERE RATING   EQ 'G' OR 'NR'
  ON RATING    SUBTOTAL AVE. LISTPR AS '*Ave:  '
  ON CATEGORY  SUBTOTAL SUM. COPIES AS '*Sum:  '
END

The output is:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR  TITLE
------  --------  ------  ------  -----------  -----
G       CHILDREN       2   44.95        29.99  SHAGGY DOG, THE
                       2   29.95        12.50  ALICE IN WONDERLAND
                       3   26.99        12.00  BAMBI

*Sum:   CHILDREN       7

        CLASSIC        3   89.95        40.99  GONE WITH THE WIND

*Sum:   CLASSIC        3
*Ave:   G                  47.96

NR      CHILDREN       1   19.95        10.00  SMURFS, THE
                       1   19.95         9.75  SCOOBY-DOO-A DOG IN THE
                       1   14.95         7.65  SESAME STREET-BEDTIME S
                       1   14.98         7.99  ROMPER ROOM-ASK MISS MO
                       1   29.95        15.99  SLEEPING BEAUTY

*Sum:   CHILDREN       5

        CLASSIC        1   24.98        14.99  EAST OF EDEN
                       3   39.99        20.00  CITIZEN KANE
                       1   29.95        15.99  CYRANO DE BERGERAC
                       1   19.99        10.95  MARTY
                       2   19.99        10.95  MALTESE FALCON, THE
                       2   19.95         9.99  ON THE WATERFRONT
                       2   89.99        40.99  MUTINY ON THE BOUNTY
                       2   19.99        10.95  PHILADELPHIA STORY, THE
                       2   19.98        10.99  CAT ON A HOT TIN ROOF
                       2   29.95        15.00  CASABLANCA

*Sum:   CLASSIC       18
*Ave:   NR                 27.64


TOTAL                 33   31.91

Example: Using SUBTOTAL at the Sort Break and Grand Total Levels

The following example adds the ON TABLE SUBTOTAL command to the request in the previous example (Using Prefix Operators With SUBTOTAL) at the sort break level to calculate the minimum number of copies and maximum list price on the grand total line for the entire report:

TABLE FILE MOVIES
PRINT COPIES LISTPR WHOLESALEPR TITLE/A23
  BY RATING
  BY CATEGORY
  WHERE CATEGORY EQ 'CHILDREN' OR 'CLASSIC'
  WHERE RATING   EQ 'G' OR 'NR'
  ON RATING    SUBTOTAL AVE. LISTPR AS '*Ave:  '
  ON CATEGORY  SUBTOTAL SUM. COPIES AS '*Sum:  '
  ON TABLE SUBTOTAL MIN. COPIES MAX. LISTPR
END

The output is exactly the same as in the previous request, except for the grand total line:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR  TITLE
------  --------  ------  ------  -----------  -----
G       CHILDREN       2   44.95        29.99  SHAGGY DOG, THE
                       2   29.95        12.50  ALICE IN WONDERLAND
                       3   26.99        12.00  BAMBI

*Sum:   CHILDREN       7

        CLASSIC        3   89.95        40.99  GONE WITH THE WIND

*Sum:   CLASSIC        3
*Ave:   G                  47.96

NR      CHILDREN       1   19.95        10.00  SMURFS, THE
                       1   19.95         9.75  SCOOBY-DOO-A DOG IN THE
                       1   14.95         7.65  SESAME STREET-BEDTIME S
                       1   14.98         7.99  ROMPER ROOM-ASK MISS MO
                       1   29.95        15.99  SLEEPING BEAUTY

*Sum:   CHILDREN       5

        CLASSIC        1   24.98        14.99  EAST OF EDEN
                       3   39.99        20.00  CITIZEN KANE
                       1   29.95        15.99  CYRANO DE BERGERAC
                       1   19.99        10.95  MARTY
                       2   19.99        10.95  MALTESE FALCON, THE
                       2   19.95         9.99  ON THE WATERFRONT
                       2   89.99        40.99  MUTINY ON THE BOUNTY
                       2   19.99        10.95  PHILADELPHIA STORY, THE
                       2   19.98        10.99  CAT ON A HOT TIN ROOF
                       2   29.95        15.00  CASABLANCA

*Sum:   CLASSIC       18
*Ave:   NR                 27.64


TOTAL                  1   89.99

Example: Differentiating Between Fields With Multiple Prefix Operators

The following request uses both the MAX. and MIN. prefix operators with the UNITS field. On the summary commands, these are differentiated by referencing them as MAX.UNITS and MIN.UNITS.

TABLE FILE GGSALES
   SUM MAX.UNITS MIN.UNITS
     BY REGION
   BY ST
   ON REGION RECOMPUTE MAX.  MAX.UNITS MIN. MIN.UNITS
   WHERE DATE GE 19971001
   WHERE REGION EQ 'West' OR 'Northeast'
   ON TABLE RECOMPUTE MIN. MAX.UNITS MAX. MIN.UNITS
   ON TABLE SET PAGE NOPAGE
   END

On the report output, the summary for each region displays the maximum of the state maximum values and the minimum of the state minimum values. The summary for the entire report displays the minimum of the state maximum values and the maximum of the state minimum values. The report output is shown in the following image:

Example: Displaying an Alphanumeric Field on a Summary Line

The following request displays the sum of the list price field and the minimum value of the director field by rating:

TABLE FILE MOVIES
PRINT COPIES LISTPR WHOLESALEPR DIRECTOR
BY RATING
BY CATEGORY
WHERE CATEGORY EQ 'CHILDREN' OR 'CLASSIC'
WHERE RATING   EQ 'G' OR 'NR'
WHERE DIRECTOR NE ' '
ON RATING SUBTOTAL SUM. LISTPR MIN. DIRECTOR AS '*A/N:'
END

The output is:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR  DIRECTOR
------  --------  ------  ------  -----------  --------
G       CHILDREN       2   44.95        29.99  BARTON C.
                       2   29.95        12.50  GEROMINI
                       3   26.99        12.00  DISNEY W.
        CLASSIC        3   89.95        40.99  FLEMING V

*A/N: G                   191.84               BARTON C.

NR      CHILDREN       1   29.95        15.99  DISNEY W.
        CLASSIC        1   24.98        14.99  KAZAN E.
                       3   39.99        20.00  WELLES O.
                       1   29.95        15.99  GORDON M.
                       1   19.99        10.95  MANN D.
                       2   19.99        10.95  HUSTON J.
                       2   19.95         9.99  KAZAN E.
                       2   89.99        40.99  MILESTONE L.
                       2   19.99        10.95  CUKOR G.
                       2   19.98        10.99  BROOKS R.
                       2   29.95        15.00  CURTIZ M.

*A/N: NR                  344.71               BROOKS R.


TOTAL                     536.55               BARTON C.

Example: Displaying All Fields on a Summary Line

The following request displays the sum of every display field on the subtotal line. The director field is alphanumeric, so the last value displays:

TABLE FILE MOVIES
PRINT COPIES LISTPR WHOLESALEPR DIRECTOR
BY RATING
BY CATEGORY
WHERE CATEGORY EQ 'CHILDREN' OR 'CLASSIC'
WHERE RATING   EQ 'G' OR 'NR'
WHERE DIRECTOR NE ' '
ON RATING SUBTOTAL SUM. * AS '*All:  '
END

The output is:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR  DIRECTOR
------  --------  ------  ------  -----------  --------
G       CHILDREN       2   44.95        29.99  BARTON C.
                       2   29.95        12.50  GEROMINI 
                       3   26.99        12.00  DISNEY W.
        CLASSIC        3   89.95        40.99  FLEMING V

*All:   G             10  191.84        95.48  FLEMING V

NR      CHILDREN       1   29.95        15.99  DISNEY W.
        CLASSIC        1   24.98        14.99  KAZAN E. 
                       3   39.99        20.00  WELLES O.
                       1   29.95        15.99  GORDON M.
                       1   19.99        10.95  MANN D.  
                       2   19.99        10.95  HUSTON J.
                       2   19.95         9.99  KAZAN E. 
                       2   89.99        40.99  MILESTONE L.
                       2   19.99        10.95  CUKOR G.
                       2   19.98        10.99  BROOKS R.
                       2   29.95        15.00  CURTIZ M.

*All:   NR            19  344.71       176.79  CURTIZ M.


TOTAL                 29  536.55       272.27  CURTIZ M.

Controlling Summary Line Processing

How to:

Reference:

When processing summary lines, you can control whether SUBTOTAL and RECOMPUTE commands are propagated to the grand total row of a report.

If the summary line contains fields with and without prefix operators, those fields without prefix operators are processed as though they were specified with the operator SUM.

The function of the SET SUMMARYLINES command is to make the processing of SUBTOTAL, SUB-TOTAL, SUMMARIZE, and RECOMPUTE on the grand total line consistent with how they work for sort field breaks. The setting that invokes this type of processing is SET SUMMARYLINES=EXPLICIT.

When SUBTOTAL and RECOMPUTE are used at a sort break level, they do not propagate to other sort breaks. SUB-TOTAL and SUMMARIZE propagate to all higher level sort breaks.

The grand total can be considered the highest level sort field in a request. However, by default, all of the summary options, not just SUB-TOTAL and SUMMARIZE, propagate to the grand total level.

The SET SUMMARYLINES=EXPLICIT command prevents the propagation of SUBTOTAL and RECOMPUTE to the grand total. In addition, if all summary commands in the request specify field lists, only the specified fields are aggregated and displayed on the grand total line.

When SUBTOTAL and RECOMPUTE are the only summary commands used in the request, a grand total line is produced only if it is explicitly specified in the request using the ON TABLE SUBTOTAL/SUB-TOTAL/RECOMPUTE/SUMMARIZE phrase. If the ON TABLE phrase specifies a field list, only those fields are aggregated and displayed.

Note that you can always suppress the grand total line using the ON TABLE NOTOTAL command in the request.

Syntax: How to Control Summary Line Processing

SET SUMMARYLINES = {NEW|OLD|EXPLICIT}

where:

NEW

Propagates all summary operations to the grand total line. Fields listed in a summary command are populated only on summary lines created by that summary command and on summary lines created by propagation of that summary command. NEW is the default value.

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.

OLD

This value is no longer supported and is processed as NEW.

EXPLICIT
Does not propagate SUBTOTAL and RECOMPUTE to the grand total line. Fields listed in a summary command are populated only on summary lines created by that summary command and on summary lines created by propagation of that summary command.

Note: This command is not supported in a request using the ON TABLE SET syntax.

Reference: Usage Notes for SET SUMMARYLINES

  • SET SUMMARYLINES is not supported within a TABLE request (ON TABLE).
  • If COLUMN-TOTAL is specified in the request, all numeric fields are totaled on the grand total line unless the COLUMN-TOTAL phrase lists specific fields. If the COLUMN-TOTAL phrase lists specific fields, those fields and any fields propagated by SUB-TOTAL or SUMMARIZE commands are totaled.
  • A summary command with a list of field names populates only those columns on the associated summary line.

For example:

TABLE FILE MOVIES 
PRINT COPIES LISTPR WHOLESALEPR
 BY RATING
 BY CATEGORY
 WHERE CATEGORY EQ 'CHILDREN'
 WHERE RATING   EQ 'G'
 ON RATING    SUBTOTAL LISTPR AS '*LIST' 
 ON CATEGORY  SUBTOTAL  COPIES AS '*COPY'
END

The output has subtotals for COPIES on the CATEGORY sort break and for LISTPR on the RATING sort break. Both columns are populated on the grand total line. WHOLESALEPR is not referenced in either SUBTOTAL command and, therefore, is not on any summary line:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR
------  --------  ------  ------  -----------
G       CHILDREN       2   44.95        29.99
                       2   29.95        12.50
                       3   26.99        12.00

*COPY CHILDREN         7
*LIST G                   101.89


TOTAL                  7  101.89

Example: Using SET SUMMARYLINES With SUBTOTAL

The following request using the MOVIES data source has a sort break for CATEGORY that subtotals the COPIES field and a sort break for RATING that subtotals the LISTPR field:

TABLE FILE MOVIES
SUM COPIES LISTPR WHOLESALEPR
BY RATING
BY CATEGORY
WHERE CATEGORY EQ 'CHILDREN'
WHERE RATING   EQ 'G'
ON RATING SUBTOTAL COPIES
ON CATEGORY SUBTOTAL LISTPR
END

Running the request with SUMMARYLINES=NEW subtotals COPIES only for the RATING sort break and subtotals LISTPR only for the CATEGORY sort break but propagates both to the grand total line:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR
------  --------  ------  ------  -----------
G       CHILDREN       7  101.89        54.49

*TOTAL CHILDREN           101.89
*TOTAL G               7


TOTAL                  7  101.89

Running the request with SUMMARYLINES=EXPLICIT subtotals COPIES only for the RATING sort break and subtotals LISTPR only for the CATEGORY sort break. It does not produce a grand total line:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR
------  --------  ------  ------  -----------
G       CHILDREN       7  101.89        54.49

*TOTAL CHILDREN           101.89
*TOTAL G               7

Adding the phrase ON TABLE SUBTOTAL WHOLESALEPR with SUMMARYLINES=EXPLICIT produces a grand total line with the WHOLESALEPR field subtotaled:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR
------  --------  ------  ------  -----------
G       CHILDREN       7  101.89        54.49

*TOTAL CHILDREN           101.89
*TOTAL G               7


TOTAL                                   54.49

Example: Using COLUMN-TOTAL With SET SUMMARYLINES=EXPLICIT

The following request using the MOVIES data source has a sort break for CATEGORY for which subtotals the COPIES field and a sort break for RATING that subtotals the LISTPR field. It also has an ON TABLE COLUMN-TOTAL phrase:

SET SUMMARYLINES=EXPLICIT
TABLE FILE MOVIES
SUM COPIES LISTPR WHOLESALEPR
BY RATING
BY CATEGORY
WHERE CATEGORY EQ 'CHILDREN'
WHERE RATING   EQ 'G'
ON RATING SUBTOTAL COPIES
ON CATEGORY SUBTOTAL LISTPR
ON TABLE COLUMN-TOTAL
END

The grand total line displays a column total for all numeric columns because of the ON TABLE COLUMN-TOTAL phrase:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR
------  --------  ------  ------  -----------
G       CHILDREN       7  101.89        54.49

*TOTAL CHILDREN           101.89
*TOTAL G               7


TOTAL                  7  101.89        54.49

The following request has an ON TABLE SUBTOTAL WHOLESALEPR command. It also has an ON TABLE COLUMN-TOTAL phrase:

SET SUMMARYLINES=EXPLICIT
TABLE FILE MOVIES
SUM COPIES LISTPR WHOLESALEPR
BY RATING
BY CATEGORY
WHERE CATEGORY EQ 'CHILDREN'
WHERE RATING   EQ 'G'
ON RATING SUBTOTAL COPIES
ON CATEGORY SUBTOTAL LISTPR
ON TABLE SUBTOTAL WHOLESALEPR
ON TABLE COLUMN-TOTAL
END

The grand total line displays a column total only for the WHOLESALEPR column because of the ON TABLE SUBTOTAL command:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR
------  --------  ------  ------  -----------
G       CHILDREN       7  101.89        54.49

*TOTAL CHILDREN           101.89
*TOTAL G               7


TOTAL                                   54.49

Using SUB-TOTAL instead of SUBTOTAL causes COPIES and LISTPR to be aggregated on the grand total line. WHOLESALEPR is totaled because it is listed in the COLUMN-TOTAL phrase. The subtotal for LISTPR propagates to the RATING sort break as well as to the grand total:

SET SUMMARYLINES=EXPLICIT
TABLE FILE MOVIES
SUM COPIES LISTPR WHOLESALEPR
BY RATING
BY CATEGORY
WHERE CATEGORY EQ 'CHILDREN'
WHERE RATING   EQ 'G'
ON RATING SUB-TOTAL COPIES
ON CATEGORY SUB-TOTAL LISTPR
ON TABLE COLUMN-TOTAL WHOLESALEPR
END

The output is:

RATING  CATEGORY  COPIES  LISTPR  WHOLESALEPR
------  --------  ------  ------  -----------
G       CHILDREN       7  101.89        54.49

*TOTAL CHILDREN           101.89
*TOTAL G               7  101.89


TOTAL                  7  101.89        54.49

Using Prefix Operators With Calculated Values

If a request includes the RECOMPUTE or SUMMARIZE command, the expression specified in the associated COMPUTE command is applied using values from the summary line. The columns used to recompute the expression can have prefix operators. The recomputed column, regardless of the prefix operator specified for it, applies these input values to the expression specified in the COMPUTE command. Therefore, any supported prefix operator can be specified for the recomputed report column without affecting the calculated value.

All fields used in the COMPUTE command must be displayed by the RECOMPUTE or SUMMARIZE command in order to be populated. If any field used in the expression is not populated, the calculated value returned for the expression is unpredictable.

Example: Using Prefix Operators With RECOMPUTE

The first request creates a calculated field named DIFF, which is the difference between DOLLARS and BUDDOLLARS. This value is then recomputed for each region, without using prefix operators.

TABLE FILE GGSALES
SUM UNITS DOLLARS BUDDOLLARS
AND COMPUTE DIFF/I10 = DOLLARS-BUDDOLLARS;
  BY REGION
  BY CATEGORY
  WHERE CATEGORY EQ 'Food' OR 'Coffee'
  WHERE REGION EQ 'West' OR 'Midwest'
  ON REGION  RECOMPUTE
END

The recomputed value is the difference between the totals for DOLLARS and BUDDOLLARS.

Region      Category    Unit Sales Dollar Sales Budget Dollars       DIFF
------      --------    ---------- ------------ --------------       ----
Midwest     Coffee          332777      4178513        4086032      92481
            Food            341414      4338271        4220721     117550

*TOTAL Midwest              674191      8516784        8306753     210031

West        Coffee          356763      4473517        4523963     -50446
            Food            340234      4202337        4183244      19093

*TOTAL West                 696997      8675854        8707207     -31353


TOTAL                      1371188     17192638       17013960     178678

The following request uses prefix operators in the RECOMPUTE command to calculate the maximum DOLLARS and the minimum BUDDOLLARS and then recompute DIFF. No matter which prefix operator we specify for DIFF, it is calculated as the difference between the values in the DOLLARS and BUDDOLLARS columns. If any of the fields used in the calculation (DOLLARS, BUDDOLLARS, and DIFF) do not display on the summary row, the calculation cannot be performed.

TABLE FILE GGSALES
SUM UNITS DOLLARS BUDDOLLARS
AND COMPUTE DIFF/I10 = DOLLARS-BUDDOLLARS;
  BY REGION
  BY CATEGORY
  WHERE CATEGORY EQ 'Food' OR 'Coffee'
  WHERE REGION EQ 'West' OR 'Midwest' 
  ON REGION RECOMPUTE MAX. DOLLARS MIN. BUDDOLLARS AVE. DIFF
END

The output is:

Region      Category    Unit Sales Dollar Sales Budget Dollars       DIFF
------      --------    ---------- ------------ --------------       ----
Midwest     Coffee          332777      4178513        4086032      92481
            Food            341414      4338271        4220721     117550

*TOTAL Midwest                          4338271        4086032     252239

West        Coffee          356763      4473517        4523963     -50446
            Food            340234      4202337        4183244      19093

*TOTAL West                             4473517        4183244     290273

Example: Using RECOMPUTE at the Sort Break and Grand Total Levels

The following example adds the ON TABLE RECOMPUTE command to the previous request (Using Prefix Operators With RECOMPUTE) to calculate the average values for each column. Notice that the value of DIFF is calculated as the difference between the values in the Dollar Sales and the Budget Dollars columns on the grand total line:

TABLE FILE GGSALES
SUM UNITS DOLLARS BUDDOLLARS
AND COMPUTE DIFF/I10 = DOLLARS-BUDDOLLARS;
  BY REGION
  BY CATEGORY
  WHERE CATEGORY EQ 'Food' OR 'Coffee'
  WHERE REGION EQ 'West' OR 'Midwest'
  ON REGION  RECOMPUTE MAX. DOLLARS MIN. BUDDOLLARS DIFF
 ON TABLE RECOMPUTE AVE.
END

The output is:

Region      Category    Unit Sales Dollar Sales Budget Dollars       DIFF
------      --------    ---------- ------------ --------------       ----
Midwest     Coffee          332777      4178513        4086032      92481
            Food            341414      4338271        4220721     117550

*TOTAL Midwest                          4338271        4086032     252239

West        Coffee          356763      4473527        4523963     -50436
            Food            340234      4202338        4183244      19094

*TOTAL West                             4473527        4183244     290283


TOTAL                       342797      4298162        4253490      44672

Using Multiple SUB-TOTAL or SUMMARIZE Commands With Prefix Operators

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 different prefix operators 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 and to the grand total line, unless a different operator is specified for the grand total line.

Example: Using Multiple SUB-TOTAL Commands With Prefix Operators

The following illustrates prefix operators work in a request that has multiple SUB-TOTAL commands, each with a different prefix operator.

DEFINE FILE GGSALES
YEAR/YY = DATE;
END
 
TABLE FILE GGSALES
SUM   UNITS DOLLARS/D10.2 BUDDOLLARS
  BY YEAR
  BY ST
  BY REGION
  BY CATEGORY
WHERE REGION EQ 'West' OR 'Midwest'
WHERE ST     EQ 'CA' OR 'IL'
WHERE YEAR EQ '1996' OR '1997'
  ON YEAR SUB-TOTAL CNT. UNITS AS '*CNT. UNITS:'
  ON ST SUB-TOTAL AVE. DOLLARS AS '*AVE. $:' 
  ON REGION SUB-TOTAL MIN. AS '*MIN.:'
END

In the following report output, some of the values have been manually italicized or bolded for clarity:

  • Outlined rows are the rows generated by the SUB-TOTAL commands.
  • Subtotal values in the normal typeface are the count of unit sales generated by the command ON YEAR SUB-TOTAL CNT. UNITS. This is the topmost summary command, and therefore does not propagate to any other summary lines.
  • Subtotal values in italic are average dollar sales generated by the command ON ST SUB-TOTAL AVE. DOLLARS. This is the second summary command, and therefore propagates to the DOLLARS column of summary lines for the YEAR sort field.
  • Subtotal values in boldface are minimums within their sort groups generated by the command ON REGION SUB-TOTAL MIN. This is the last summary command, and therefore propagates to all other summary lines, but only calculates minimum values for those columns not already populated with a count or an average.