A to Z of Excel Functions: The CUBERANKEDMEMBER Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the CUBERANKEDMEMBER function.

The CUBERANKEDMEMBER function

When the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source, this function returns the nth, or ranked, member in a set. Use this to return one or more elements in a set, such as the top sales performer or the top 10 students.

The CUBERANKEDMEMBER function employs the following syntax to operate:

The CUBERANKEDMEMBER function has the following arguments:

• connection: this is required and represents a text string of the name of the connection to the cube
• set_expression: this is also required.  This is a text string of a set expression, such as "{[Item1].children}"
• set_expression can also be the CUBESET function or a reference to a cell that contains the CUBESET function
• rank: again, this is required.  This is an integer value specifying the top value to return.  If rank is a value of 1, it returns the top value, if rank is a value of 2, it returns the second most top value, and so on.  To return the top 5 values, use CUBERANKEDMEMBER five times, specifying a different rank, 1 through 5, each time
• caption: this argument is optional.  This is a text string displayed in the cell instead of the caption, if one is defined, from the cube.

It should be further noted that: 

• the CUBERANKEDMEMBER function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source
• when the CUBERANKEDMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved
• if the connection name is not a valid workbook connection stored in the workbook, CUBERANKEDMEMBER returns an #NAME? error value.  If the Online Analytical Processing (OLAP) server is not running, not available or returns an error message, CUBERANKEDMEMBER returns an #NAME? error value
• CUBERANKEDMEMBER returns an #N/A error value when the syntax of set_expression is incorrect or when the set contains at least one member with a different dimension than the other members
• to return the bottom n values instead, use the sort_order and sort_by arguments of the CUBESET function to reverse the order of the set so that the top values in the sorted set are the bottom values.  For example, CUBERANKEDMEMBER ("Sales",$D$4,1) returns the last member, CUBERANKEDMEMBER ("Sales",$D$4,2) returns the next to last member, and so on.

Please see my examples below:

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.