Definition This function returns the n-th member in a set.
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.
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/A error 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/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
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.