NonEmptyCount

The MDX NonEmptyCount function for Essbase returns the count of the number of tuples in a set that evaluate to non-#Missing values.

Each tuple is evaluated and included in the count returned by this function. If the numeric value expression is specified, it is evaluated in the context of every tuple, and the count of non-#Missing values is returned.

On aggregate storage databases, the NonEmptyCount MDX function is optimized so that the calculation of the distinct count for all cells can be performed by scanning the database only once. Without this optimization, the database is scanned as many times as the number of cells corresponding to the distinct count. The NONEMPTYCOUNT optimization is triggered when an outline member formula has the following syntax:

NONEMPTYCOUNT(set, measure, exclude_missing)

Syntax

NonEmptyCount ( set [,numeric_value_expression [, exclude_missing ]] )

Parameters

set

The set in which to count tuples.

numeric_value_expression

Optional. (See MDX Grammar Rules.)

exclude_missing

Optional. A flag that indicates that the count value returned is missing when the Measure value is missing for members in Set.

Where:

Set: Is a one dimensional set from a stored dimension.
Measure: Is a stored measure.

The exclude_missing parameter supports the NonEmptyCount optimization on aggregate databases by improving the performance of a query that queries metrics that perform a distinct count calculation. See Example 2 in this topic for more information.

By default, a value of zero is returned when the Measure value is missing for all members in the Set.

Example

Example 1

The following query

With 
Member [Measures].[Number Of Markets]
as 'NonEmptyCount (Market.Levels(0).Members, Sales)'

Select 
{[Measures].[Number Of Markets]} on Columns,
{[100].Children, [200].Children} on Rows
FROM Sample.Basic

Returns the grid:

Table 4-117 Output Grid from MDX Example

(axis)	Number of Markets
100-10	20
100-20	16
100-30	8
200-10	20
200-20	17
200-30	9
200-40	3

Example 2

In an aggregate storage database, it is common to count the distinct number of entities (such as customers and products). You can perform a distinct count by defining a formula member or a calculated member. For example, you can add a formula member, [DistinctCustomerCnt], to use with the following formula to calculate the count of distinct customers who bought a Product.

NONEMPTYCOUNT(Customer.Levels(0).Members, [Units])

The following MDX query scans the database as many times as the number of Products, evaluating the distinct customer count for each Product separately:

SELECT
   {[DistinctCustomerCnt]} on COLUMNS,
   Products.Levels(0).Members on  ROWS