Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02

## RUNTOTAL

The RUNTOTAL function returns the running total of an expression. You can use the RUNTOTAL function in a ROW command, ROW function, or REPORT command to generate a running total of the value of an expression.

Return Value

DECIMAL

Syntax

RUNTOTAL(n)

Arguments

n

One of the 32 subtotals (1 to 32) that Oracle OLAP accumulates for the current column of a report. RUNTOTAL returns the value of this subtotal for the specified column, but does not reset the value of the subtotal to zero.

Notes

How RUNTOTAL Works

Unlike the SUBSTR function, RUNTOTAL does not reset the indicated subtotal to zero, nor does it add the value returned by RUNTOTAL to the indicated subtotal. However, the value returned by RUNTOTAL is added to the other 31 accumulating totals for the current column.

Accessing Data from Another Column

You can obtain a running total of an expression shown in another column of a report by adding that expression to RUNTOTAL. You can use the COALESCE function to refer to the values in the other column. For example, to show the sales for each month in the first data column of a row, and a cumulative total of sales in the second data column, you could use this statement.

```ROW month sales COLVAL(-1) + RUNTOTAL(1)

```

Resetting Subtotals

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 REPORT command automatically resets all subtotals to zero before producing output.

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

RUNTOTAL ignores `NA` values unless all values are `NA`. When all values are `NA`, the total is `NA`.

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 21-21 Calculating a Running Total in a Report

In a report, suppose you want column 2 to contain a running total of the values in column 1.

Assume that you issue the following OLAP DML statements

```ZEROTOTAL ALL
ROW W 4 R 2 RUNTOTAL(1) + COLVAL(1)
ROW W 4 R 5 RUNTOTAL(1) + COLVAL(1)
ROW W 4 R 3 RUNTOTAL(1) + COLVAL(1)

```

These statements produce the following output.

```2     2.00
5     7.00
3    10.00
```

Example 21-22 Calculating a Running Total over Two Districts

In this example, you want your report to contain the unit sales of tents for two districts for the first six months of 1996. Along with the monthly sales figures, you want to see a running total of tent sales for these two districts for the year to date. To produce this cumulative total, use the RUNTOTAL function.

```LIMIT product TO 'Tents'
LIMIT month TO 'Jan96' TO 'Jun96'
LIMIT district TO 'Boston' 'Chicago'
REPORT ACROSS district: units -
DECIMAL 0 TOTAL(units, month)+RUNTOTAL(1)

```

These statements produce the following output.

```PRODUCT: TENTS
--------UNITS--------
------DISTRICT-------
TOTAL(UNIT
S,
MONTH)+RUN
MONTH            Boston    Chicago    TOTAL(1)
-------------- ---------- ---------- ----------
Jan96                 307        189        496
Feb96                 209        190        895
Mar96                 277        257      1,429
Apr96                 372        318      2,119
May96                 525        433      3,077
Jun96                 576        466      4,119
```