Cube Functions in Microsoft Excel 2010

CUBEMEMBERPROPERTY()

Syntax CUBEMEMBERPROPERTY(connection,member_expression,property)

Definition This function returns the property of a member from the cube. Use CUBEMEMBERPROPERTY() to validate that a member exists within the cube and to return the property for this member as a value.

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

• member_expression (required) Defines the position of a member 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.

• property (required) The name of the property for which you want to return the value.

Figure 14-6 Checking whether cube members have properties—not all members do.

Background In the example in this section, the stores have the Group property with the possible values North or South (shown previously in Figure 14-5).

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 wrong or missing entries:

• If the connection name is not a valid workbook connection, the CUBEMEMBERPROPERTY() 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 the member_expression syntax is incorrect, or if the member specified by member_expression doesn’t exist in the cube, the CUBEMEMBERPROPERTY() function returns the #N/A error.

• CUBEMEMBERPROPERTY() might return the #N/A error when the connection to the data source is interrupted and cannot be re-established

Example As previously mentioned, the stores in the PivotTable have the Group properties North and South. The formula

=CUBEMEMBERPROPERTY("offline","[Stores].[Store].[All].[NorthEast]","group")

returns North, and the formula

=CUBEMEMBERPROPERTY("offline","[Stores].[Store].&[3]","group")

returns South. This example uses the position number of the store in the list instead of the store name.