Rank

Returns the numeric position of a tuple in a set.

Syntax

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

Parameter	Description
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.
DENSERANK	Optional. Rank with no gaps in ordinals.
PERCENTRANK	Optional. Rank on a scale from 0 to 1.

Notes

This function is applicable only to aggregate storage databases.

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, this 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. The meanings of the flags are:

[no flag]: Default behavior. Ties are given the same rank, and the next member is the count of members. Example:(1,1,1,4,5)
ORDINALRANK: Ties are decided by Essbase. Duplicates are considered different entities. Example: (1,2,3,4,5).
DENSERANK: Ties are given the same rank, but there are no gaps in ordinals. Example: (1,1,1,2,3)
PERCENTRANK: Rank values are scaled by the cumulative sum up to this member. Example: ( .1, .15, .34, .78, 1.0). Values range from 0.0 to 1.0.

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.Sample;

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.Sample;