RANK
Returned Type
This function returns a number.
Syntax and Details
RANK(member_expression, set_expression, optional_numeric_expression)
Where:
member_expression is an expression that returns a member.
set_expression is an expression that returns a set.
optional_numeric_expression is a numeric-valued expression that the function evaluates for each member in the set.
Typically, this expression is [MEASURES].[measure_name]
If you specify this argument, the system evaluates this expression for the member and for every other member in the set. The system then returns an integer that indicates how the given member ranks compared to these other members. The member with the lowest value is at position 1.
If you do not specify this argument, the system returns the ordinal position of the member within the given set. The first position is 1.
Example
For example, the following query shows the rank of the member colord.green within the set of members of the colord dimension, when the members are ranked by patient count:
SELECT RANK(colord.green, colord.MEMBERS, MEASURES.[%COUNT]) ON 0 FROM patients
Green
Results 2
To see that this is correct, consider the following query, which sorts the members of this dimension by the patient count:
SELECT MEASURES.[%COUNT] ON 0,
ORDER(colord.MEMBERS, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 None 1,243
2 Green 1,304
3 Blue 2,381
4 Orange 1,302
5 Purple 1,276
6 Red 1,244
7 Yellow 1,250