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:
- ASQ.
- AVE.
- CNT.
- FST.
- LST.
- MAX.
- MIN.
- SUM.
The following prefix operators are supported
for alphanumeric fields:
- FST.
- LST.
- MAX.
- MIN.
- SUM. (means
LST. if SUMPREFIX=LST or FST. if SUMPREFIX=FST)
x
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.
x
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 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.
xControlling Summary Line Processing
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.
x
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.
x
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.89Running
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 7Adding
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.49The 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.49Using 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
xUsing 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 178678The 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 request in 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
xUsing 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.
