CoalesceEmpty

The MDX CoalesceEmpty function for Essbase returns the first (from the left) non #Missing value from the given value expressions.

Syntax

CoalesceEmpty ( numeric_value_expression1, numeric_value_expression2 )

Parameters

numeric_value_expression1: A numeric value expression (see MDX Grammar Rules).
numeric_value_expression2: A numeric value expression (see MDX Grammar Rules).

Notes

This function returns numeric_value_expression2 if numeric_value_expression1 is #MISSING; otherwise it returns numeric_value_expression1.

Example

CoalesceEmpty([Profit per Ounce], 0)

returns the [Profit per Ounce] value if it is not #MISSING; returns zero otherwise. This can be used inside the Order function to coalesce all #MISSING values to zero, as shown in the next example:

Order([Product].Members, CoalesceEmpty([Profit per Ounce], 0))

Without CoalesceEmpty in the value expression, the Order function would skip all [Product] members with MISSING values for [Profit per Ounce].