TopCount

The MDX TopCount() function for Essbase returns a set of n elements ordered from largest to smallest, optionally based on an evaluation. This function ignores missing values.

Syntax

TopCount ( set , index [,numeric_value_expression ] )

Parameters

set: The set from which the top n elements are selected.
index: The number of elements to include in the set (n).
numeric_value_expression: Optional. An expression further defining the selection criteria (see MDX Grammar Rules).

Example

The following MDX query selects the five top-selling markets in terms of yearly Diet products sales, and displays the quarterly sales for each Diet product:

SELECT
 CrossJoin(
           [Product].[Diet].Children,
           [Year].Children
          ) 
ON COLUMNS,
 TopCount(
          [Market].Levels(0).Members,
          5, 
          [Product].[Diet]
         ) 
ON ROWS
FROM Sample.Basic
WHERE ([Scenario].[Actual], [Measures].[Sales])

The above returns the following output:

Table 4-132 Output Grid from MDX Example

(axis)	100-20	100-20	100-20	100-20	200-20	200-20	200-20	200-20	300-30	300-30	300-30	300-30
(axis)	Qtr1	Qtr2	Qtr3	Qtr4	Qtr1	Qtr2	Qtr3	Qtr4	Qtr1	Qtr2	Qtr3	Qtr4
Illinois	755	958	1050	888	1391	1520	1562	1402	675	755	859	894
California	367	491	506	468	1658	1833	1954	1706	700	802	880	673
Colorado	700	802	880	673	549	465	412	539	1006	921	892	991
Washington	637	712	837	704	459	498	597	514	944	799	708	927
Iowa	162	153	121	70	129	129	129	129	1658	1833	1954	1706