Count

The MDX Count function for Essbase returns the number of tuples in a set (the cardinality of the set). This function counts all tuples of the set regardless of empty values.

If you wish to count only tuples that evaluate to nonempty values, use NonEmptyCount.

Syntax

Count ( set [, IncludeEmpty] )

Parameters

set

The set for which a tuple count is needed.

IncludeEmpty

Optional and default (empty values are counted even if this keyword is omitted).

Notes

This function returns a zero if the input set is empty.

Example

WITH MEMBER 
 [Measures].[Prod Count] 
AS 
 'Count ( 
    Crossjoin ( 
     {[Measures].[Sales]}, 
     {[Product].children} 
    ) 
  )' 
SELECT 
 { [Scenario].[Actual], [Scenario].[Budget] } 
ON COLUMNS, 
 { 
  Crossjoin ( 
     {[Measures].[Sales]}, 
     {[Product].children} 
    ), 
   ([Measures].[Prod Count], [Product])
 } 
ON ROWS 
FROM 
 Sample.Basic 
WHERE 
 ([Year].[Jan], [Market].[New York])

returns the grid:

Table 4-47 Output Grid from MDX Example

(axis)		Actual	Budget
Sales	Colas	678	640
	Root Beer	551	530
	Cream Soda	663	510
	Fruit Soda	587	620
	Diet Drinks	#Missing	#Missing
Prod Count	Product	5	5

The WITH section of the query calculates the count of all products for which a data value exists. The SELECT section arranges the members shown on columns and rows. The entire query is sliced by January and New York in the WHERE section; though those members are not shown in the grid, the data is applicable to those members.