Skip Headers
Previous
Previous
 
Next
Next

Rank

Orders the members of a dimension based on the values of the selected measure.

You can choose a method for handling identical values:

Template

Rank members of the dimension dimension and hierarchy hierarchy based on measure measure (...). Calculate rank using { RANK | DENSE_RANK | AVERAGE_RANK } method with { member's level | member's parent | ancestor at level level } in order { highest to lowest | lowest to highest }. Rank NA (null) values { nulls last | nulls first }.

Example

This calculated measure lists Products with the same parent in descending order based on the values of the Units measure.

Calculation

Rank members of the PRODUCT dimension and PRODUCT.PRIMARY hierarchy based on measure UNITS (...). Calculate rank using RANK method with member's parent in order highest to lowest. Rank NA (null) values nulls last.

OLAP Expression Syntax

RANK() OVER HIERARCHY (PRODUCT.PRIMARY ORDER BY UNITS_CUBE.UNITS DESC NULLS LAST WITHIN PARENT)

Query Results

The Rank column lists Hardware and Software products in descending order according to the number of Units sold.

Product               Parent   Units Rank
--------------------- ------ ------- ----
CD/DVD                HRD     442316    1
Modems/Fax            HRD     395389    2
Desktop PCs           HRD     291119    3
Memory                HRD     138200    4
Monitors              HRD     116963    5
Portable PCs          HRD      87963    6
Accessories           SFT    1793534    1
Operating Systems     SFT     506853    2
Documentation         SFT     228631    3
Software/Other        TOTAL  2529018    1
Hardware              TOTAL  1471950    2