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. Because 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.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.
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.
SUBTOTAL ignores 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. Because 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