Counting Empty Cells

OLE DB Programmer's Reference

Functions such as COUNT and AVERAGE evaluate a count of cells, but whether or not to evaluate an empty cell by this type of function should be given some careful forethought. In practice, it sometimes makes sense to count the number of empty cells. For example, when counting the number of sales reps for purposes of performance evaluation, all sales reps 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.

MDX allows for both these scenarios. All numeric functions other than COUNT ignore empty cells. If empty cells need to be included in the count, the consumer can use the COALESCEEMPTY function to force a cell to be treated as a "regular" cell. The COUNT function has an optional argument, INCLUDEEMPTY, that includes empty cells in the count. In the absence of INCLUDEEMPTY, COUNT ignores empty cells.

1998-2001 Microsoft Corporation. All rights reserved.