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.
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.
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.
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)
When you use a ROW statement to produce a report, you can use a ZEROTOTAL statement to reset any subtotal of any column to zero. Typically, use ZEROTOTAL at the beginning of a report program to make sure all totals begin at zero. A REPORT statement automatically resets all subtotals to zero before producing output.
RUNTOTAL ignores NA values unless all values are NA. When all values are NA, the total is NA.
Example 8-94 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 8-95 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