1. Calculation and Query Reference for Oracle Essbase
  2. MDX
  3. MDX Function List
  4. Rank

Rank

The MDX Rank function for Essbase returns the numeric position of a tuple in a set.

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.

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

This 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

This 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