TOPCOUNT
Returned Type
This function returns a set of members or tuples, depending on the set used.
Syntax and Details
TOPCOUNT(set_expression, element_count, optional_ordering_expression)
Where:
-
set_expression is an expression that evaluates to a set of members or tuples.
-
element_count is an integer literal.
The function uses this argument to determine the number of elements to return in the subset. If this argument is greater than the number of elements, all elements are returned.
-
optional_ordering_expression is a numeric-valued expression that determines the order of the set elements.
Typically, ordering_expression is [MEASURES].[measure_name]
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.
If this argument is omitted, the function uses the current order of the set elements (and this function behaves like the HEAD function).
Example
First consider the following query and the results it returns:
SELECT MEASURES.[%COUNT] ON 0,
TOPCOUNT(birthd.decade.MEMBERS, 100, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 1970s 1,520
2 1960s 1,500
3 2000s 1,433
4 1990s 1,413
5 1980s 1,400
6 1950s 1,030
7 1940s 683
8 1930s 572
9 1920s 223
10 2010s 155
11 1910s 71
Because count_expression is greater than the number of members, all members are returned. The members are sorted in ascending order according to the value of the %COUNT measure.
Next, consider a similar query, using count_expression equal to 3:
SELECT MEASURES.[%COUNT] ON 0,
TOPCOUNT(birthd.decade.MEMBERS, 3, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 1970s 1,520
2 1960s 1,500
3 2000s 1,433
This query selects three members from the higher-valued end of the set.
