|Oracle® OLAP DML Reference
11g Release 2 (11.2)
Part Number E17122-05
The SUBTOTAL function returns the value of one subtotal 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 a REPORT statement, use the GRANDTOTALS and SUBTOTALS keywords to include rows of grand totals and subtotals.
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.
The numbers by which the 32 subtotals are referenced (1 to 32) have no intrinsic significance; all the subtotals are the same until you reference them.
Resetting Subtotals Automatically
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.
Resetting Subtotals with ZEROTOTAL
When you use the ROW command to produce a report, you can use a ZEROTOTAL statement to reset any subtotal of any column to zero.Typically, use ZEROTOTAL this at the beginning of a report program to make sure all totals begin at zero.
NA Values and SUBTOTAL
NA values. When all values are
NA, SUBTOTAL returns zero.
Example 8-124 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