|
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)
Controlling 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.
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
