Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
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

Go to previous page
Go to next page
View PDF


The ZEROROW option suppresses report rows with numeric values that are all NAs or all zeros or would be represented as zeros. ZEROROW affects output produced by the REPORT and ROW commands.

Data type






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


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


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.

Values Close to Zero

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 will be used to calculate the total, even when the number is suppressed.

The Effect of NASPELL

The value of NASPELL does not affect the way ZEROROW handles NA values.

The Effect of ZSPELL

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 24-48 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