|Oracle® OLAP DML Reference
10g Release 1 (10.1)
Part Number B10339-02
The SUBTOTAL function returns the value of one of the subtotals accumulated in a report. You normally use the SUBTOTAL function in a ROW command to include a subtotal or grand total in the report. Since Oracle OLAP maintains 32 running totals for each column, you can include up to 32 levels of subtotals
Note:In the REPORT command, use the GRANDTOTALS and SUBTOTALS keywords to include rows of grand totals and subtotals.
An integer value that indicates the level of a running total for each numeric column in a report. For example, a "Total" may be a level 1 subtotal and a "Grand Total" may be a level 2 subtotal. Because it is possible to have up to 32 levels of running totals in a column, n must be an integer between 1 and 32. SUBTOTAL returns the value of this subtotal for the current column and then resets the value of subtotal n to zero.
When you use the SUBTOTAL function in a ROW command to include a subtotal of the current column, the subtotal at that level is reset to zero.
When you use the ROW command to produce a report, you can use the ZEROTOTAL command to reset any subtotal of any column to zero. Normally, you should do this at the beginning of a report program to make sure all totals begin at zero.
The numbers by which the 32 subtotals are referenced (
32) have no intrinsic significance. All the subtotals are the same until you reference them.
NA values. When all values are
NA, SUBTOTAL returns zero.
When a "decimal overflow" condition occurs while subtotals are being accumulated (that is, an out-of-range value is generated), all subtotals for the affected column are set to
NA and processing continues when the DECIMALOVERFLOW option is set to
YES. The subtotals for the column will continue to be
NA until they are reset by a ZEROTOTAL command. When DECIMALOVERFLOW is
NO, an error occurs when a decimal overflow condition occurs.
Example 23-3 Calculating Subtotals and Grand Totals in a Report
In a sales report, suppose you want to show a subtotal for each region. You also want to see a grand total of all sales at the end of the report. You can use
SUBTOTAL(1) to produce the subtotal for each region. This subtotal is reset to
0 each time you use it, so it provides a separate subtotal for each region. At the end of the report you can use
SUBTOTAL(2) to produce the grand total. Since you have not yet used it in your report, it holds a total of the sales figures for all regions.
LIMIT month TO FIRST 3 LIMIT region TO ALL ZEROTOTAL ALL FOR region DO ROW region LIMIT DISTRICT TO region FOR district DO ROW INDENT 5 district ACROSS month: sales DOEND ROW INDENT 5 'Total' ACROSS month: OVER '-' SUBTOTAL(1) BLANK DOEND ROW 'Grand Total' ACROSS month: OVER '=' SUBTOTAL(2)
The program produces the following output.
East Boston 32,153.52 32,536.30 43,062.75 Atlanta 40,674.20 44,236.55 51,227.06 ---------- ---------- ---------- Total 72,827.72 76,772.85 94,289.81 Central Chicago 29,098.94 29,010.20 39,540.89 Dallas 47,747.98 50,166.81 67,075.44 ---------- ---------- ---------- Total 76,846.92 79,177.01 106,616.33 West Denver 36,494.25 33,658.24 45,303.93 Seattle 43,568.02 41,191.28 51,547.23 ---------- ---------- ---------- Total 80,062.27 74,849.52 96,851.16 ========== ========== ========== Grand Total 229,736.91 230,799.38 297,757.30