How to: Reference: |
Using SUMMARIZE and RECOMPUTE, you can recalculate values at sort field breaks, but these calculations use the detail data to calculate the value for the summary line.
Using the ROLL. operator in conjunction with another prefix operator on a summary line recalculates the sort break values using the values from summary lines generated for the lower level sort break.
The operator combinations supported are:
ROLL.prefix on a summary line indicates that the prefix operation will be performed on the summary values from the next lowest level of summary command.
If the ROLL. operator is used without another prefix operator, it is treated as a SUM. Therefore, if the summary command for the lowest BY field specifies AVE., and the next higher specifies ROLL., the result will be the sum of the averages. To get the average of the averages, you would use ROLL.AVE at the higher level.
Note: With SUMMARIZE and SUB-TOTAL, the same calculations are propagated to all higher level sort breaks.
BY field {SUMMARIZE|SUBTOTAL|SUB-TOTAL|RECOMPUTE} [ROLL.][prefix1.] [field1 field2 ...|*] [ROLL.][prefix2.] [fieldn ...]
Or:
BY fieldON field {SUMMARIZE|SUBTOTAL|SUB-TOTAL|RECOMPUTE} ROLL.[prefix.] [field1 field2 ...|*]
where:
Indicates that the summary values should be calculated using the summary values from the next lowest level summary command.
Is a BY field in the request.
Are prefix operators to use for the summary values. Can be one of the following operators: SUM. (the default operator if none is specified), AVE., MAX., MIN., FST., LST., CNT., ASQ.
Are fields to be summarized.
Indicates that all fields, numeric and alphanumeric, should be included on the summary lines. You can either use the asterisk to display all columns or reference the specific columns you want to display.
The following request against the GGSALES data source contains two sort fields, REGION and ST. The summary command for REGION applies the AVE. operator to the sum of the units value for each state.
TABLE FILE GGSALES
SUM UNITS AS 'Inventory '
BY REGION
BY ST
ON REGION SUBTOTAL AVE. AS 'Average'
WHERE DATE GE 19971001
WHERE REGION EQ 'West' OR 'Northeast'
ON TABLE SET PAGE NOPAGE
END On the output, the UNITS values for each state are averaged to calculate the subtotal for each region. The UNITS values for each state are also used to calculate the average for the grand total row.
Region State Inventory
------ ----- ----------
Northeast CT 37234
MA 35720
NY 36248
Average Northeast
36400
West CA 75553
WA 40969
Average West
58261
TOTAL 45144The following version of the request adds a summary command for the grand total line that includes the ROLL. operator:
TABLE FILE GGSALES
SUM UNITS AS 'Inventory '
BY REGION
BY ST
ON REGION SUBTOTAL AVE. AS 'Average'
WHERE DATE GE 19971001
WHERE REGION EQ 'West' OR 'Northeast'
ON TABLE SUBTOTAL ROLL.AVE.
ON TABLE SET PAGE NOPAGE
END On the output, the UNITS values for each state are averaged to calculate the subtotal for each region, and those region subtotal values are used to calculate the average for the grand total row:
Region State Inventory
------ ----- ----------
Northeast CT 37234
MA 35720
NY 36248
Average Northeast
36400
West CA 75553
WA 40969
Average West
58261
TOTAL 47330
The following request against the GGSALES data source has three BY fields. The SUBTOTAL command for the PRODUCT sort field specifies AVE., and the SUMMARIZE command for the higher level sort field, REGION, specifies ROLL.AVE.
TABLE FILE GGSALES SUM UNITS BY REGION BY PRODUCT BY HIGHEST DATE WHERE DATE GE 19971001 WHERE REGION EQ 'Midwest' OR 'Northeast' WHERE PRODUCT LIKE 'C%' ON PRODUCT SUBTOTAL AVE. ON REGION SUMMARIZE ROLL.AVE. ON TABLE SET PAGE NOPAGE END
On the output, the detail rows for each date are used to calculate the average for each product. Because of the ROLL.AVE. at the region level, the averages for each product are used to calculate the averages for each region, and the region averages are used to calculate the average for the grand total line:
Region Product Date Unit Sales
------ ------- ---- ----------
Midwest Coffee Grinder 1997/12/01 4648
1997/11/01 3144
1997/10/01 1597
*TOTAL PRODUCT Coffee Grinder 3129
Coffee Pot 1997/12/01 1769
1997/11/01 1462
1997/10/01 2346
*TOTAL PRODUCT Coffee Pot 1859
Croissant 1997/12/01 7436
1997/11/01 5528
1997/10/01 6060
*TOTAL PRODUCT Croissant 6341
*TOTAL REGION Midwest 3776
Northeast Capuccino 1997/12/01 1188
1997/11/01 2282
1997/10/01 3675
*TOTAL PRODUCT Capuccino 2381
Coffee Grinder 1997/12/01 1536
1997/11/01 1399
1997/10/01 1315
*TOTAL PRODUCT Coffee Grinder 1416
Coffee Pot 1997/12/01 1442
1997/11/01 2129
1997/10/01 2082
*TOTAL PRODUCT Coffee Pot 1884
Croissant 1997/12/01 4291
1997/11/01 6978
1997/10/01 4741
*TOTAL PRODUCT Croissant 5336
*TOTAL REGION Northeast 2754
TOTAL 3265
| iWay Software |