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.ZEROTOTAL with no arguments resets all subtotals in all columns to zero.
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.
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
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.
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.
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 RUNTOTALRUNTOTAL function.
Example 10-177 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