ZEROTOTAL

The ZEROTOTAL command resets one or all subtotals of specified report columns to zero. You use the ZEROTOTAL command when you produce reports with the ROW command.

Note:

ZEROTOTAL affects the results returned by the RUNTOTAL and SUBSTR functions.

Syntax

ZEROTOTAL [{n|ALL} [column1 columnN]]

Arguments

ZEROTOTAL with no arguments resets all subtotals in all columns to zero.

n

An INTEGER expression that specifies one of the 32 subtotals (1 to 32) Oracle OLAP accumulates for each numeric column in a report. For the specified columns, this subtotal is set to zero.

ALL

Sets all 32 subtotals to zero for the specified columns. ALL is the default when there are no arguments. To reset all the subtotals to zero for specific columns, you must include ALL in the statement.

ZEROTOTAL ALL 1 4 7

column

The column number of a report column. Column number 1 refers to the left-most column in a report, regardless of the type of data it contains. When you do not supply any column number arguments, Oracle OLAP sets the specified subtotal (or all subtotals) to zero for all the columns in the report.

Notes

Initializing Column Subtotals

When you use the ROW command to produce a report, use a ZEROTOTAL statement at the beginning of the report program to initialize all 32 subtotals for all columns to zero. The REPORT statement automatically resets all subtotals to zero before producing output.

Resetting Column Subtotals

You can also use ZEROTOTAL in a report program when you only want to reset some subtotals or when you want to start accumulating new subtotals without inserting the subtotals accumulated so far. A subtotal is automatically reset to zero after it is accessed with the SUBSTR function in its own column. However, a subtotal is not reset to zero after it is accessed with the RUNTOTAL function.

Examples

Example 24-50 Resetting All Report Column Subtotals

In a report, you want to show a dollar sales total, followed by a detailed summary of unit sales for each district. You also want to show a total for unit sales at the end of the report, but you do not want the dollar sales figures included in that total. After generating the total dollar sales, use ZEROTOTAL to reset all your subtotals to zero. Then when you use SUBTOTAL(1) later in the report, it only totals the unit sales for each district.

Suppose you have these statement lines in your program.

LIMIT product TO 'Footwear'
LIMIT month TO 'Jul96' TO 'Sep96'
ROW 'Total Dollar Sales' ACROSS month: -
   DECIMAL 0 TOTAL(sales month)
BLANK
ROW 'Unit Sales'
ZEROTOTAL ALL
FOR district
   ROW INDENT 5 district ACROSS month: units
ROW 'Total Unit Sales' ACROSS month: -
   OVER '-' SUBTOTAL(1)

These statements produce the following output.

Total Dollar Sales      607,552    581,229    658,850
 
Unit Sales
     Boston               3,538      3,369      3,875
     Atlanta              4,058      3,866      4,251
     Chicago              3,943      3,509      4,058
     Dallas                 814        824        867
     Denver               1,581      1,532      1,667
     Seattle              2,053      2,193      2,617
                     ---------- ---------- ----------
Total Unit Sales      15,987.00  15,293.00  17,335.00