Missing Data in Essbase

Working With Missing Data:

Note: Replace L with "less than" symbol and G with "greater than" symbol:
When you are querying on a database, you can use the NON EMPTY keywords at the beginning of an axis specification to prevent cells containing no value from being included the result of the query.

The axis specification syntax including NON EMPTY is :

Laxis_specificationG ::= [NON EMPTY] LsetG ON
COLUMNS | ROWS | PAGES | CHAPTERS |
SECTIONS | AXIS ()

Including the optional keywords NON EMPTY before the set specification in an axis causes suppression of slices in that axis that would contain entirely #MISSING values.

For any given tuple on an axis (such as (Qtr1, Actual)), a slice consists of the cells arising from combining this tuple with all tuples of all other axes. If all of these cell values are #MISSING, the NON EMPTY keyword causes the tuple to be eliminated.

For example, if even one value in a row is not empty, the entire row is returned. Including NON EMPTY at the beginning of the row axis specification would eliminate the following row slice from the set returned by a query:

---------------------------------------------------------
Qtr1
--------------------------------------------------------------
Actual   #MISSING   #MISSING   #MISSING   #MISSING   #MISSING
--------------------------------------------------------------

In addition to suppressing missing values with NON EMPTY, you can use the following MDX
functions to handle #MISSING results:
● CoalesceEmpty, which searches numeric value expressions for non #MISSING values
● IsEmpty, which returns TRUE if the value of an input numeric-value-expression evaluates
to #MISSING.
● Avg, which omits missing values from averages unless you use the optional IncludeEmpty
flag.