This function returns the n-th member in a set.
Syntax
CUBERANKEDMEMBER(connection; set_expression; rank; caption)
Arguments
- connection (required): A string representing the name of the workbook connection to the cube. After you type the first quotation mark, existing context-sensitive data connections are displayed.
- set_expression (required): Defines the number of members in the cube based on a Multidimensional Expression (MDX). The expression can be entered directly or referenced from a cell. Tuples can also be used in expressions.
- rank (required): An integer indicating the position of a member within the set.
- caption (required): A string to be displayed in the cell instead of the member’s caption from the cube. If a tuple is used, the function returns the caption of the last member in the tuple.
Background
Error values and messages provide information about incorrect or missing entries:
- If the connection name is not a valid workbook connection, the CUBERANKEDMEMBER() function returns the #NAME? error.
- If the OLAP server (or the offline cube) is not available, an error message will appear, but the content of the affected cell will not change.
- If set_expression is longer than 255 characters, the CUBERANKEDMEMBER() function returns the #VALUE! error.
- CUBERANKEDMEMBER() returns the #N/A error when:
- The set_expression syntax is incorrect.
- The set specified in the MDX query does not exist in the cube.
- CUBERANKEDMEMBER() might also return the #N/A error when the connection to the data source is interrupted and cannot be re-established.
Example
If you reference a cell in the formula =CUBERANKEDMEMBER(« offline »,B9,1) that returns the store set with =CUBESET(« offline », »[Stores].[Store].Children », « all store sales »,2, »[Measures].[Sale] »), the result is NorthEast. This store has the most sales for all products and in all years.
The nested formula =CUBERANKEDMEMBER(« offLine »,CUBESET(« offLine », »([Stores].[Store].[All].[NorthEast], [Years].Children) », »all sales »,2; »[Measures].[Sales] »),3) calculates the year with the least sales for this store (position 3): 2011.
