Empty Cells

OLE DB Programmer's Reference

Usually cubes have empty data points (cells). For example (using SalesCube from the SalesData example), the SalesRep Venkatrao sells products only in the USA, so the point corresponding to the coordinates (Venkatrao, Canada, Years.[All], Quarters.[All], Sales) has no data. In fact, no point has data whose coordinates are (Venkatrao, Geography.x, Years.<any>, Quarters.<any>, Sales), where x <> USA and <any> represents any member of a dimension.

Empty cells affect the evaluation of value expressions and search conditions. This is because 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>, while others 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:

• 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.
• In a string value expression, this value can be concatenated to another string.
• A search condition is 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 above.

For further discussion relating to empty cells and value expressions, see the following topics in this section:

• Semantics of Empty Cell Value
• COALESCEEMPTY Function
• Counting Empty Cells
• Empty Cells in a Dataset

1998-2001 Microsoft Corporation. All rights reserved.