How to Use the CUBERANKEDMEMBER Function in Excel
Today, we’ll explore the CUBERANKEDMEMBER function, a powerful tool for those working with OLAP (Online Analytical Processing) data sources in Excel and Google Sheets. This function is critical for retrieving specific ranked elements or members from a data set within a cube structure.
Syntax
The CUBERANKEDMEMBER function follows this syntax:
CUBERANKEDMEMBER(connection, member_expression, set_expression, rank, )connection: A text string that specifies the OLAP database connection.member_expression: A text string that identifies the member within the set to be ranked.set_expression: A text string that defines the set in which the ranking is applied.rank: A numeric value that specifies the rank of the member to be retrieved.caption(optional): A boolean value determining whether to return the member’s name or unique identifier.
Examples
Consider an example where we have a dataset of sales data in an OLAP cube and we wish to identify the top 3 products by sales amount.
Excel
In Excel, you can utilize the CUBERANKEDMEMBER function as follows:
| Product Rank | Product Name |
|---|---|
| 1 | =CUBERANKEDMEMBER(“OLAPDB”, “[Measures].[Sales Amount]”, “[Product].[Product Name].Members”, 1, TRUE) |
| 2 | =CUBERANKEDMEMBER(“OLAPDB”, “[Measures].[Sales Amount]”, “[Product].[Product Name].Members”, 2, TRUE) |
| 3 | =CUBERANKEDMEMBER(“OLAPDB”, “[Measures].[Sales Amount]”, “[Product].[Product Name].Members”, 3, TRUE) |
Google Sheets
In Google Sheets, you would use the CUBERANKEDMEMBER function identically:
| Product Rank | Product Name |
|---|---|
| 1 | =CUBERANKEDMEMBER(“OLAPDB”, “[Measures].[Sales Amount]”, “[Product].[Product Name].Members”, 1, TRUE) |
| 2 | =CUBERANKEDMEMBER(“OLAPDB”, “[Measures].[Sales Amount]”, “[Product].[Product Name].Members”, 2, TRUE) |
| 3 | =CUBERANKEDMEMBER(“OLAPDB”, “[Measures].[Sales Amount]”, “[Product].[Product Name].Members”, 3, TRUE) |
Using the CUBERANKEDMEMBER function allows you to efficiently extract ranked members from OLAP cubes, significantly enhancing your analytical capabilities.
More information: https://support.microsoft.com/en-us/office/cuberankedmember-function-07efecde-e669-4075-b4bf-6b40df2dc4b3