CUBEMEMBER()

Syntax

CUBEMEMBER(connection; member_expression; caption)

Definition

This function returns a member (cell) from a cube. Use CUBEMEMBER() to validate the existence of a member and to pass that member to other functions via a cell reference.

Arguments

• connection (required): The text string name of the workbook connection to the cube, enclosed in quotation marks. As you begin typing the connection (after the first quotation mark). member_expression (required): Defines the position of a member in the cube based on a multidimensional expression (MDX). This expression can be entered directly or referenced from a cell. You can also use tuples within these expressions.
• caption (optional): 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

When you use CUBEMEMBER() as an argument for another cube function, the MDX expression, rather than the displayed value, is utilized 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 unavailable, an error message will appear, but the content of the affected cell will not 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 does not 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 if the connection to the data source is interrupted and cannot be re-established.

Example

To better understand the use of the functions in this section, let’s examine the PivotTable in Figure below closely.

The formula =CUBEMEMBER(« offLine »; »[Products].[Product].[All].[Cookies] »)

searches for a single cell and returns the « Cookies » member, which has the caption we were looking for.

If you use the tuple: =CUBEMEMBER(« offLine »; « ([Stores].[Store].[All].[NorthEast],[Products].[All].[Cookies],[Years].[2008]) ») the result is 2008 (representing the cookie sales in the year 2008 in the NorthEast store).

If you use: =CUBEMEMBER(« offLine »; « ([Stores].[Group].[All].[North],[Stores].[Store].[All].[NorthEast] ») to find an empty intersection, you will 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.