Cube Functions in Microsoft Excel 2010

CUBERANKEDMEMBER()

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

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

=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.