Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF


For output produced by the REPORT and ROW commands, the ZEROROW option suppresses report rows with numeric values that are all NAs or all zeros or would be represented as zeros. When your report includes a small number, such as 0.004, the number of decimal places being shown affects whether ZEROROW treats that number as zero. When you are producing a report with totals, the actual number is used to calculate the total, even when the number is suppressed.

Data Type






Suppresses report rows that contain any numeric values when all the numeric values would be shown either as zeros or NAs.


(Default) Produces all rows of the report, regardless of the values they contain.

Usage Notes

Non-Numeric Data

Even when a row contains non-numeric data, such as TEXT, ID, or BOOLEAN values, along with numeric values, the row is suppressed when ZEROROW is YES and all the numeric values would be shown either as zeros or NAs.

The Effect of NASPELL and ZSPELL

The value of NASPELL does not affect the way ZEROROW handles NA values. The value of ZSPELL does not affect the functioning of ZEROROW; numeric zero values are treated as zeros regardless of their spelling in output.


Example 5-117 Suppressing Report Rows of All-Zero Data

Suppose you have a variable called worstcase, that is dimensioned by division, month, and line, in which you store the results of calculations to project sales. When you produce a report of the results, you want to suppress any rows for which the value of the worst-case projections is zero for all months in the status. Set ZEROROW to YES, as shown in the following statements.

LIMIT line TO 'Revenue'
LIMIT month TO 'Nov95' TO 'Feb96'
REPORT WIDTH 8 DOWN division ACROSS month: worstcase

These statements produce the following report.

DIVISION   Nov95      Dec95      Jan96      Feb96
-------- ---------- ---------- ---------- ----------
Camping        0.00       0.00  45,500.00  47,400.00
Sporting       0.00       0.00  29,200.00  28,400.00
Clothing       0.00       0.00  15,200.00  14,900.00

In the preceding report, no rows are suppressed, since some months for each division have projected sales. However, when you lay out this report with month down and division across, the rows for Nov95 and Dec95 are suppressed, because these months have no projected sales.

REPORT DOWN month ACROSS division: worstcase

This statement produces the following report.

MONTH           Camping    Sporting   Clothing
-------------- ---------- ---------- ----------
Jan96           45,500.00  29,200.00  15,200.00
Feb96           47,400.00  28,400.00  14,900.00