How to Use the CUBEMEMBERPROPERTY Function in Excel
Welcome to the guide on using the CUBEMEMBERPROPERTY function in Microsoft Excel and Google Sheets. This function is essential for retrieving properties of specific members within Online Analytical Processing (OLAP) cubes. Through this guide, you will learn how to use the CUBEMEMBERPROPERTY function, explore typical use cases, and understand how to effectively employ this function in practical situations.
Overview
The CUBEMEMBERPROPERTY function extracts properties from a cube member or tuple. It requires the following arguments:
- Member_expression: Identifies the cube member or tuple whose property you want to extract.
- Property: Specifies the property of the member to retrieve, such as MEMBER_CAPTION, MEMBER_KEY, MEMBER_NAME, etc.
- Cube_name: The name of the cube within the cube database. This argument is optional in certain contexts.
Examples
Below are examples demonstrating different use cases for the CUBEMEMBERPROPERTY function.
Example 1: Retrieve the Member Name
This example shows how to obtain the name of a specific member within a cube. Here’s the function syntax:
=CUBEMEMBERPROPERTY(Member_expression, "MEMBER_NAME")To apply this in a practical scenario:
| Member | Name |
|---|---|
| [Product].[Category].&[1] | =CUBEMEMBERPROPERTY(“[Product].[Category].&[1]”, “MEMBER_NAME”) |
Example 2: Retrieve the Member Caption
In this example, the goal is to retrieve the caption for a particular member in a cube. Use the following syntax:
=CUBEMEMBERPROPERTY(Member_expression, "MEMBER_CAPTION")Here is how you might use this:
| Member | Caption |
|---|---|
| [Date].[2019].[Q1] | =CUBEMEMBERPROPERTY(“[Date].[2019].[Q1]”, “MEMBER_CAPTION”) |
Implementation
To implement the CUBEMEMBERPROPERTY function in Excel or Google Sheets, follow these steps:
- Select the cell where you want the result to display.
- Type in the CUBEMEMBERPROPERTY function with the necessary arguments.
- Press Enter to display the property value.
Before using the CUBEMEMBERPROPERTY function, ensure your spreadsheet is connected to an OLAP cube data source.
By following these guidelines and examples, you’ll be able to effectively utilize the CUBEMEMBERPROPERTY function to extract detailed information about cube members in both Excel and Google Sheets.
More information: https://support.microsoft.com/en-us/office/cubememberproperty-function-001e57d6-b35a-49e5-abcd-05ff599e8951