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