Syntax. CUBERANKEDMEMBER(connection,set_expression,rank,caption)
Definition. This function returns the n-th member in a set.
Arguments
connection (required) A string with the name of the workbook connection to the cube. After you enter the first quotation mark, the existing context-sensitive data connections are displayed (see Figure 14-4, shown earlier).
set_expression (required) Defines the number of members in the cube based on an MDX. The expression can be entered directly or can be in a cell that is referenced. You can also use tuples in expressions.
rank (required) An integer indicating the position of a member in the set.
caption (required) A string displayed in the cell instead of the caption of the member in the cube. If a tuple is used, the function returns the caption of the last member in the tuple.
Background
Note
In the cell containing the function, the message #GETTING_DATA temporarily appears while the data is being queried.
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, you get an error message. The content of the affected cell doesn’t change.
If set_expression is longer than 255 characters, the CUBERANKEDMEMBER() function returns the
#VALUE!error.CUBERANKEDMEMBER() returns the
#N/Aerror when:The set_expression syntax is incorrect.
The set specified in the MDX query doesn’t exist in the cube.
CUBERANKEDMEMBER() might return the
#N/Aerror 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.