TOPPERCENT (MDX)

Returned Type

This function returns a set of members or tuples, depending on the set used.

Syntax and Details

TOPPERCENT(set_expression, percentage, ordering_expression)

• set_expression is an expression that evaluates to a set of members or tuples.

• percentage is a numeric literal that is less than or equal to 100. That is, 15 represents 15 percent.

  The function uses this argument to determine the cutoff point for elements to return in the subset.

  There is usually a member that straddles the cutoff point; this member is assigned to the upper set, rather than the lower set. As a result, in the returned subset, the cumulative total for ordering_expression could be greater than percentage, as a percentage of the entire set.

• ordering_expression is a numeric-valued expression that determines the order of the set elements.

  The function evaluates this expression for each element of the set and sorts the elements of the set in descending order according to this value. Any hierarchies are ignored.

Example

First consider the following query and the results it returns:

SELECT MEASURES.[%COUNT] ON 0, 
TOPPERCENT(birthd.decade.MEMBERS, 100, MEASURES.[%COUNT]) ON 1 FROM patients
 
                             Patient Count
 1 2000s                                157
 2 1980s                                155
 3 1990s                                144
 4 1960s                                136
 5 1970s                                128
 6 1950s                                107
 7 1930s                                 56
 8 1940s                                 54
 9 2010s                                 44
10 1920s                                 13
11 1910s                                  6

Because percentage is 100, all members are returned.

Now consider a variation of the preceding, in which percentage is 50, so that we see the top 50 percent:

SELECT MEASURES.[%COUNT] ON 0, TOPPERCENT(birthd.decade.MEMBERS, 50, MEASURES.[%COUNT]) ON 1 FROM patients
 
                             Patient Count
1 2000s                                 157
2 1980s                                 155
3 1990s                                 144
4 1960s                                 136

The total for the %COUNT measure for these members is a little more than 50% of the total. (If the 1960s were omitted, the total count would be less than 50%.)

See Also

• BOTTOMPERCENT