Please note javascript is required for full website functionality.
MVP

Blog

A to Z of Excel Functions: the CUBERANKEDMEMBER Function

12 March 2018

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:

CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

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:

=CUBERANKEDMEMBER("Sales",$D$4,1,"Top Month")

=CUBERANKEDMEMBER("Sales",CUBESET("Sales","Summer","[2020].[June]","[2020].[July]","[2020].[August]"),3,"Top Month")

 

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

Newsletter