The CUBEMEMBERPROPERTY function in Excel returns the value of an element attribute within a cube. To delve deeper into information about the CUBEMEMBERPROPERTY function, examples, and how to use the function, readers can further explore the article below by Mytour.
The CUBEMEMBERPROPERTY function.
1. Description of the CUBEMEMBERPROPERTY function in Excel
The CUBEMEMBERPROPERTY function is one of the Excel functions belonging to the Cube group. Excel's CUBEMEMBERPROPERTY function returns the value of an element attribute within a cube. This function is used to verify the name of an existing element within the cube and returns the specified attribute for this element.
2. Syntax of the CUBEMEMBERPROPERTY function in Excel, returning the value of an element attribute within a cube
The syntax of the CUBEMEMBERPROPERTY function in Excel is as follows:
CUBEMEMBERPROPERTY(connection, member_expression, property)
Where:
- The Connection parameter is mandatory, it is the text string name of the connection to the cube.
- The Member_expression parameter is mandatory. It is a text string multidimensional expression (MDX) of an element within a cube.
- The Property parameter is mandatory. It is the text string name of the property returned by the function referencing the cell containing the name of the property.
3. Some notes on the CUBEMEMBERPROPERTY function in Excel
- When the CUBEMEMBERPROPERTY function evaluates, it will temporarily display '#GETTING_DATA...' in the cell before all data is retrieved.
- If the connection name is not a valid connection stored in the workbook, the CUBEMEMBERPROPERTY function will return the error message #NAME?. If the Online Analytical Processing (OLAP) server is not working, unavailable, or returns an error message, the CUBEMEMBERPROPERTY function will return the error #NAME?.
- If the member_expression syntax is incorrect or if the element specified by member_expression does not exist within the cube, the CUBEMEMBERPROPERTY function returns the error #N/A.
- The CUBEMEMBERPROPERTY function may return #N/A error if you reference an object based on the session such as calculated element or set name in a PivotTable when sharing connection and PivotTable is deleted or you convert PivotTable to formulas. (in the Options tab, in the Tools group, click OLAP Tools, then click Convert to Formulas).
- The CUBEMEMBERPROPERTY function will not work with Excel Data Model edited in Power Pivot, as they are not multidimensional cubes.
4. Example of using CUBEMEMBERPROPERTY function in Excel to return the value of an element attribute within a cube
Below are some examples of using the CUBEMEMBERPROPERTY function in Excel to return the value of an element attribute within a cube:
=CUBEMEMBERPROPERTY('Sales','[Time].[Fiscal].[2014]',$A$3)
=CUBEMEMBERPROPERTY('Sales','[Store].[MyFavoriteStore]','[Store].[Store Name].[Store Sqft]')
Mytour's article just introduced you to Excel's CUBEMEMBERPROPERTY function, which returns the value of an element attribute within a cube. Additionally, readers can explore other Excel functions such as the ISNA function, CUBEMEMBER function in Excel,... If you have any questions or need clarification, please feel free to leave your comments below the article, and Mytour will respond to your inquiries as soon as possible.
Using the Proper Case function in spreadsheets across thousands of rows will significantly save you processing time compared to manually performing each string. To learn more about the syntax and implementation of the Proper Case function in spreadsheets, follow our previously shared article.