SUBTOTAL

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.

Return Value

DECIMAL

Syntax

SUBTOTAL(n)

Arguments

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.

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

Referring to Subtotals

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.

NA Values

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

Decimal Overflow

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.

Examples

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