Skip to main content

RANK (MDX)

Returns an integer that indicates the rank of the given member, within the given set.

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
FeedbackOpens in a new tab