table of contents

Empty cells occur in Multidimensional Expressions (MDX) statements when data for the intersection of two or more dimensions does not exist. For example, the following MDX query example produces many empty cells:

```
SELECT
{[Store].[Store Name].Members} ON COLUMNS,
{[Product].[Excellent Diet Cola]} ON ROWS
FROM Sales
WHERE [Measures].[Unit Sales]
```

The product, Excellent Diet Cola, is not sold in all stores. For the stores that sell the product, the Unit Sales measure will contain a numeric value. For the stores that do not sell the product, however, an empty cell will be displayed.

Empty cells affect the evaluation of value expressions and search conditions.
To understand why this is so, note that a value expression is composed of value
expression primaries. One of the value expression primaries is
`<tuple>[.VALUE]`

, which returns the value of a cell in the
cube (some of whose coordinates are specified explicitly by
`<tuple>,`

and others that are available implicitly from the
context of the MDX statement). This cell can be an empty cell. Empty cells
affect expression evaluation in the following three cases:

- With numeric value expressions. In a numeric value expression, this value
can be added, subtracted, multiplied, or divided by other values. It can also
appear as the parameter of any function that has a
<numeric_value_expression> argument.

- With string value expressions. In a string value expression, this value can
be concatenated to another string.

- With search conditions composed of Boolean primaries. A Boolean primary is
of the following form:
`<boolean_primary> ::= <value_expression> <comp_op> <value_expression>`

A value expression will be made up of the value expression primary, and this will lead to the first two cases described listed earlier.

MDX specifically identifies an empty cell by defining a special empty cell value that is present in an empty cell. The empty cell value is evaluated as follows:

- The function
**IsEmpty**(<value_expression>) returns TRUE if <value_expression> is the empty cell value. Otherwise it returns FALSE.

- When the empty cell value is an operand for any of the numeric operators (+,
-, *, /), it behaves like the number zero.

- When the empty cell value is an operand for the string concatenation
operator (||), it behaves like the empty string.

- When the empty cell value is an operand for any of the comparison operators
(=. <>, >=, <=, >, <), it behaves like the number zero or the
empty string, depending on whether the data type of the other operand is numeric
or string, respectively.

- When collating numeric values, the empty cell value collates in the same
place as zero. Between the empty cell value and zero, empty collates before
zero.

- When collating string values, the empty cell value collates in the same place as the empty string. Between the empty cell value and the empty string, the empty cell value collates before an empty string.

Empty cells can be handled in a variety of ways; the easiest is to simply remove them from consideration. However, because this is not always practical in MDX, functions have been provided to deal with empty cells.

The easiest way to remove empty cells from consideration is to use the NON EMPTY keyword in an MDX query. The following example is the same MDX query example discussed earlier in this topic, but using the NON EMPTY keyword.

```
SELECT
NON EMPTY {[Store].[Store Name].Members} ON COLUMNS,
{[Product].[Excellent Diet Cola]} ON ROWS
FROM Sales
WHERE [Measures].[Unit Sales]
```

All of the stores in the first axis dimension that do not have values for the unit sales of the product are excluded from the result dataset. The empty tuples are screened out of the result dataset of the MDX query.

It is important to note that this function screens out empty tuples, not individual empty cells. Because of this, empty cells can appear in a result dataset even when the NON EMPTY keyword is used. For example, suppose you want to examine the unit sales for two different products in 1997 for each store. The following MDX query example uses the NON EMPTY keyword to screen out empty tuples:

```
SELECT
NON EMPTY CROSSJOIN ({[Product].[Excellent Diet Cola], [Product].[Fabulous Diet Cola]}, {[Time].[1997]}) ON COLUMNS,
NON EMPTY {[Store].[Store Name].Members} ON ROWS
FROM Sales
WHERE [Measures].[Unit Sales]
```

However, the result dataset resembles the following table.

Excellent Diet Soda | Fabulous Diet Soda | |
---|---|---|

1997 | 1997 | |

Store 6 |
20.00 | 11.00 |

Store 7 |
25.00 | 6.00 |

Store 24 |
11.00 | 19.00 |

Store 11 |
36.00 | 32.00 |

Store 13 |
25.00 | 22.00 |

Store 2 |
2.00 | |

Store 3 |
23.00 | 16.00 |

Store 15 |
14.00 | 17.00 |

Store 16 |
13.00 | |

Store 17 |
22.00 | 12.00 |

Store 22 |
2.00 | |

Store 23 |
4.00 | 5.00 |

The result dataset still shows three empty cells, despite the presence of the NON EMPTY keyword. The tuples created by the MDX query may contain empty cells, but the tuples themselves are not empty. For example, in the preceding result dataset, though Store 22 did not sell any of the Fabulous Diet Soda product in 1997, it did sell some of the Excellent Diet Soda product in 1997. So, the tuple created by the CROSSJOIN command does contain a member that does not evaluate to an empty cell; therefore the tuple is not considered empty and is not screened out.

This MDX function returns the first nonempty value in a list of values. It is useful when you want to replace empty cell values with another numeric or string expression.

The **CoalesceEmpty** function allows you to evaluate a series of value
expressions from left to right. The first value expression in the series that
does not evaluate to the empty cell value is returned. For example, the
following MDX query modifies the previous MDX query example to replace all of
the empty cell values in the Unit Sales measure with zero:

```
WITH MEMBER [Measures].[NonEmptyUnitSales] AS
'CoalesceEmpty(Measures.[Unit Sales], 0)'
SELECT
NON EMPTY CROSSJOIN ({[Product].[Excellent Diet Cola], [Product].[Fabulous Diet Cola]}, {[Time].[1997]}) ON COLUMNS,
NON EMPTY {[Store].[Store Name].Members} ON ROWS
FROM Sales
WHERE [Measures].[NonEmptyUnitSales]
```

The following table demonstrates the result dataset returned by the MDX query example.

Excellent Diet Soda | Fabulous Diet Soda | |
---|---|---|

1997 | 1997 | |

Store 19 |
0 | 0 |

Store 20 |
0 | 0 |

Store 9 |
0 | 0 |

Store 21 |
0 | 0 |

Store 1 |
0 | 0 |

Store 5 |
0 | 0 |

Store 10 |
0 | 0 |

Store 8 |
0 | 0 |

Store 4 |
0 | 0 |

Store 12 |
0 | 0 |

Store 18 |
0 | 0 |

HQ |
0 | 0 |

Store 6 |
20.00 | 11.00 |

Store 7 |
25.00 | 6.00 |

Store 24 |
11.00 | 19.00 |

Store 11 |
36.00 | 32.00 |

Store 13 |
25.00 | 22.00 |

Store 2 |
2.00 | 0 |

Store 3 |
23.00 | 16.00 |

Store 15 |
14.00 | 17.00 |

Store 16 |
0 | 13.00 |

Store 17 |
22.00 | 12.00 |

Store 22 |
2.00 | 0 |

Store 23 |
4.00 | 5.00 |

The values of the calculated member NonEmptyUnitSales were determined by the
**CoalesceEmpty** function. If the Unit Sales value evaluated to a nonempty
cell, the first value in the **CoalesceEmpty** statement was returned. If the
[Unit Sales] value evaluated to an empty cell value, the second value in the
**CoalesceEmpty** statement was returned. Because the **CoalesceEmpty**
function replaced all of the empty cell values with zero, the NON EMPTY keyword
has nothing to screen out, so all of the tuples in the query were valid and were
presented in the result dataset.

The way that other functions (especially calculation functions) deal with
empty cells depends on the capabilities and options that are available to those
functions. Functions such as **Count** and **Avg** evaluate a count of
cells, but whether or not to evaluate an empty cell by this type of function
should be given careful thought. In practice, it is sometimes preferable to
count the number of empty cells. For example, when the number of sales
representatives is counted as part of a performance evaluation query, all sales
representatives should be included in the count whether or not they sold
anything. In this case, each no-sale results in an empty cell. However, there
are other situations in which empty cells should not be counted, such as when
getting the average of sales over a certain domain. In this case, counting the
no-sale cells would inaccurately decrease the average.

Some MDX functions in which empty cells may change the outcome allow for the
inclusion or exclusion of empty cells as part of their calculation.
**Count**, for example, supports the use of INCLUDEEMPTY and EXCLUDEEMPTY
flags to handle the inclusion or exclusion of empty cells, respectively, while
counting.