CUBERANKEDMEMBER function

The CUBERANKEDMEMBER function in Excel is used to return a specific, ranked member from a set of members in a cube, based on their rank. It allows you to retrieve members from an OLAP cube (or a data model) by specifying their position (rank) within a set, which is particularly useful when you need to pull ranked results like the top 10 products or regions based on sales.

Syntax

CUBERANKEDMEMBER(connection, set_expression, rank, )

Parameters

• connection: The name of the connection to the OLAP cube or data model. This is typically a text string that represents a valid connection in your workbook.
• set_expression: A text string representing a set expression. The set is usually a list or range of members from which you want to retrieve the ranked member. You can use cube functions like CUBESET to define a set or a set expression directly.
• rank: A number representing the position of the member you want to retrieve. For example, 1 returns the highest-ranking member, 2 returns the second highest, and so on.
• caption (optional): A text string to display in place of the retrieved member’s name. This is optional and can be used to give a custom label to the cell.

Usage Example

Let’s say you have a cube connection named “SalesData” and you want to retrieve ranked members, such as the top-selling products, or the regions ranked by sales.

1. To retrieve the top (first-ranked) product from a set of products:

   =CUBERANKEDMEMBER("SalesData", "[Product].[Product Name].Members", 1)
   

2. To retrieve the second-ranked region based on sales:

   =CUBERANKEDMEMBER("SalesData", "[Geography].[Region].Members", 2)
   

3. To retrieve the third-ranked year from a set of years:

   =CUBERANKEDMEMBER("SalesData", "[Date].[Year].Members", 3)
   

4. Using a custom caption for the ranked member:

   =CUBERANKEDMEMBER("SalesData", "[Product].[Product Name].Members", 1, "Top Product")
   

   This will display “Top Product” in the cell instead of the actual product name, though the product data will still be used.

Using the CUBESET Function with CUBERANKEDMEMBER

You can use the CUBESET function to define a set of members and then use CUBERANKEDMEMBER to retrieve ranked members from that set.

1. Define a set of top-selling products using CUBESET:

   =CUBESET("SalesData", "[Product].[Product Name].Members", "All Products")
   

2. Retrieve the first-ranked (top) product from this set:

   =CUBERANKEDMEMBER("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members", "Top Products"), 1)
   

3. Retrieve the second-ranked product:

   =CUBERANKEDMEMBER("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members", "Top Products"), 2)
   

Key Uses

1. Top-N Reporting: The CUBERANKEDMEMBER function is ideal for creating reports that show the top N members based on a specific measure, such as the top 5 products by sales or the top 10 customers by revenue.
2. Ranked Reports: You can use CUBERANKEDMEMBER to display ranked members dynamically, allowing for better data-driven decision-making.
3. Combining with Other Cube Functions: This function is often used alongside CUBESET and CUBEVALUE to create reports that rank members based on specific criteria and return relevant data associated with them.

Common Errors

• #NAME?: This error occurs if the connection name is incorrect or if cube functions are not supported in your version of Excel.
• #N/A: This error may occur if the rank is outside the range of the set, or if the set expression does not return any valid members.

Practical Example

If you want to retrieve the top 3 products based on sales and display them in Excel:

1. Create a set of all products:

   =CUBESET("SalesData", "[Product].[Product Name].Members", "All Products")
   

2. Retrieve the top product:

   =CUBERANKEDMEMBER("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members", "All Products"), 1)
   

3. Retrieve the second-ranked product:

   =CUBERANKEDMEMBER("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members", "All Products"), 2)
   

4. Retrieve the third-ranked product:

   =CUBERANKEDMEMBER("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members", "All Products"), 3)
   

Summary

The CUBERANKEDMEMBER function is a powerful tool in Excel for retrieving specific, ranked members from a cube based on their position within a set. It is particularly useful for top-N reporting, ranking data, and generating dynamic reports that show the highest or lowest performing members (like products, regions, or customers). By using CUBERANKEDMEMBER with other cube functions, such as CUBESET and CUBEVALUE, you can create sophisticated reports in Excel that interact directly with OLAP data sources.