Cube Functions in Microsoft Excel 2010

CUBEMEMBER()

Syntax CUBEMEMBER(connection,member_expression,caption)

Definition This function returns a member (cell) from a cube. Use CUBEMEMBER() to validate that the member exists and to pass the member to other functions through a cell reference.

Arguments

• connection (required) The text string name of the workbook connection to the cube, in quotation marks. When you are entering the connection, after you type the first quotation mark, existing context-sensitive data connections are displayed (see Figure 14-4).

  

  Figure 14-4 Context sensitivity helps you enter formulas.

• member_expression (required) Defines the position of a member in the cube based on a multidimensional expression (MDX). The expression can be entered directly or it can be referenced in a cell. You can also use tuples in expressions.

• caption (optional) 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 When you use CUBEMEMBER() as an argument for another cube function, the MDX expression instead of the displayed value is used in the argument.

Error values and messages provide information about incorrect or missing entries:

• If the connection name is not a valid workbook connection, the CUBEMEMBER() 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 at least one member within the tuple is invalid, the CUBEMEMBER() function returns the #VALUE! error.

• If member_expression is longer than 255 characters, the CUBEMEMBER() function returns the #VALUE! error.

• CUBEMEMBER() returns the #N/A error when:

  • The member_expression syntax is incorrect.

  • The member specified in the MDX query doesn’t exist in the cube.

  • The tuple is invalid because there is no intersection for the specified values.

  • The set contains at least one member with a different dimension from the other members.

• CUBEMEMBER() may also return the #N/A error when the connection to the data source is interrupted and cannot be re-established.

Example So that you can gain a better understanding of the use of the functions in this section, take a close look at the PivotTable in Figure 14-5.

Figure 14-5 The candy sales PivotTable used to demonstrate the cube functions.

The formula

=CUBEMEMBER("offLine","[Products].[Product].[All].[Cookies]")

looks for a single cell and returns the Cookies member which has the caption we looked for. If you use the tuple

=CUBEMEMBER("offLine",
   "([Stores].[Store].[All].[NorthEast],[Products].[All].[Cookies],
   [Years].[2009])")

the result is 2009 (the cookie sales in the year 2009 in the NorthEast store). If you use

=CUBEMEMBER("offLine",
   "([Stores].[Group].[All].[North],[Stores].[Store].[All].[NorthEast]")

to find an empty intercept, you get the #N/A error. To display the word total, enter

=CUBEMEMBER("offLine","[Products].[Product].[All]","total")

You can use the cell containing the formula to create cell captions. The actual content of the cell is more informative if it refers to the cells with the CUBEMEMBER() entries.