A to Z of Excel Functions: The CUBEMEMBER Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the CUBEMEMBER function.

The CUBEMEMBER function

When the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source, this function returns a member or tuple from the cube. This is used to validate that the member or tuple exists in the cube.

The CUBEMEMBER function employs the following syntax to operate:

The CUBEMEMBER function employs the following syntax to operate:

The CUBEMEMBER function has the following arguments:

• connection: this is required and represents a text string of the name of the connection to the cube
• member_expression: this is also required.  This is a text string of a multi-dimensional expression (MDX) that evaluates to a unique member in the cube.  Alternatively, member_expression can be a tuple, specified as a cell range or an array constant
• caption: this is optional.  This represents a text string displayed in the cell instead of the caption, if one is defined, from the cube.  When a tuple is returned, the caption used is the one for the last member in the tuple.

It should be further noted that: 

• the CUBEKPIMEMBER function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source
• when the CUBEMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved
• when you use CUBEMEMBER as an argument to another CUBE function, the MDX expression that identifies the member or tuple is used by that CUBE function, not the displayed value in the cell of the CUBEMEMBER function
• if the connection name is not a valid workbook connection stored in the workbook, CUBEMEMBER returns a #NAME? error value.  If the Online Analytical Processing (OLAP) server is not running, not available or returns an error message, CUBEMEMBER returns a #NAME? error value
• if at least one element within the tuple is invalid, CUBEMEMBER returns an #VALUE! error value
• if member_expression is longer than 255 characters, which is the limit for an argument to a function, CUBEMEMBER returns a #VALUE! error value.  To use text strings longer than 255 characters, enter the text string in a cell (for which the limit is 32,767 characters), and then use a cell reference as the argument instead
• CUBEMEMBER returns an #N/A error value when:
  • the member_expression syntax is incorrect
  • the member specified by the MDX text string doesn't exist in the cube
  • the tuple is invalid because there is no intersection for the specified values (this can occur with multiple elements from the same hierarchy)
  • the set contains at least one member with a different dimension than the other members
• CUBEMEMBER may return an #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulae (on the ‘Options’ tab in the Ribbon, in the ‘Tools’ group, click ‘OLAP Tools’, and then click ‘Convert to Formulas’).

Please see my examples below:

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.