# SUBTOTAL

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.

Return Value

DECIMAL

Syntax

SUBTOTAL(n)

Parameters

n

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.

Usage Notes

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

SUBTOTAL ignores `NA` values. When all values are `NA`, SUBTOTAL returns zero.

Examples

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
```