Syntax

Rank ( member_or_tuple, set [,numeric_value_expr [,ORDINALRANK | DENSERANK | PERCENTRANK ]] )

Parameters

member_or_tuple

The member or tuple to rank.

set

The set containing the tuple to rank. Should not have duplicate members.

numeric_value_expr

Optional. Numeric sorting criteria.

ORDINALRANK

Optional. Rank duplicates separately. Ties are decided by Essbase. Duplicates are considered different entities. Example: (1,2,3,4,5).

DENSERANK

Optional. Rank with no gaps in ordinals. Ties are given the same rank. Example: (1,1,1,2,3)

PERCENTRANK

Optional. Rank on a scale from 0 to 1. Rank values are scaled by the cumulative sum up to this member. Example: ( .1, .15, .34, .78, 1.0).

Notes

The Rank function only applies to aggregate storage (ASO) cubes.

If no numeric value expression is given, this function returns the 1-based position of the tuple in the set.

If a numeric value expression is given, this function sorts the set based on the numeric value and returns the 1-based position of the tuple in the sorted set.

If an optional rank flag is given (ORDINALRANK, DENSERANK, or PERCENTRANK), the Rank function sorts the set based on the numeric value and returns the 1-based position of the tuple in the sorted set according to the instructions in the flag. If no rank flag is given, ties are given the same rank, and the next member is the count of members. Example:(1,1,1,4,5).

In the cases where this function sorts the set, it sorts tuples in descending order, and assigns ranks based on that order (highest value has a rank of 1).

Example

Example 1

WITH MEMBER [Measures].[Units_Rank] AS 
 'Rank(Products.CurrentMember, Products.CurrentMember.Siblings)' 
SELECT 
 {Units, [Price Paid], [Units_Rank]} 
ON COLUMNS, 
 { Products.Members } ON ROWS 
FROM ASOSamp.Basic

Example 2

WITH MEMBER [Measures].[Units_Rank] AS
  'Rank( Products.CurrentMember, Products.CurrentMember.Siblings)'
SELECT {Units, [Measures].[Units_Rank]} 
ON COLUMNS, 
 Union(Children([Televisions]),
      Children([Radios])) 
ON ROWS 
FROM ASOSamp.Basic

Example 3

The following MDX example ranks sibling tuples in terms of unit measures.

WITH MEMBER [Measures].[Units_Rank] AS 
  'Rank(([Products].CurrentMember), {[Products].CurrentMember.Siblings}, Measures.[Units])'
SELECT 
 {Units, [Price Paid], [Units_Rank]} 
ON COLUMNS, 
 { Products.Members } ON ROWS 
FROM ASOSamp.Basic

Example 4

The following MDX example ranks tuples along the level 0 descendants of an upper level member.

WITH MEMBER [Measures].[Units_Rank] AS 
  'Rank(([Products].CurrentMember),{Descendants([Products],10,LEAVES)})'
SELECT 
 {Units, [Price Paid], [Units_Rank]} 
ON COLUMNS, 
 { Products.Members } ON ROWS 
FROM ASOSamp.Basic