TopSum

The MDX TopSum function for Essbase returns the smallest possible subset of a set for which the total results of a numeric evaluation are at least a given sum. Elements of the result set are listed from largest to smallest.

Syntax

TopSum ( set, numeric_value_expression1, numeric_value_expression2 )

Parameters

set

The set from which the highest-summing elements are selected.

numeric_value_expression1

The given sum (see MDX Grammar Rules).

numeric_value_expression2

The numeric evaluation (see MDX Grammar Rules).

Notes

• If the total results of the numeric evaluation do not add up to the given sum, an empty set is returned.

• This function ignores negative and missing values.

Example

The following query selects the top-selling markets that collectively contribute 60,000 to the total yearly Diet products sales, and displays the quarterly sales for each Diet product.

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

This query returns the grid:

Table 4-135 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
Florida	620	822	843	783	548	611	657	577	332	323	260	159
Oregon	389	303	277	322	1006	921	892	991	263	231	197	184