How to Use Excel's CUBEMEMBERPROPERTY Function

By eHow Computers Editor

Rate: (0 Ratings)

Excel's CUBEMEMBERPROPERTY function returns the property of a member within the cube and is also used to verify the existence of that member. This function requires a connection with a data source.

Flag Article

Instructions

Difficulty: Moderate

Step1

Learn the syntax for CUBEMEMBERPROPERTY. It's
CUBEMEMBERPROPERTY(connection_name,member_expression,property)
where connection_name is the name of the connection to the data source, member_expression is an expression that evaluates to a member and property is the name of the property to be returned or a cell containing the property.

Step2

Expect CUBEMEMBERPROPERTY to temporarily display the text “#GETTING_DATA…” in the cell while the data is being retrieved from the data source. CUBEMEMBERPROPERTY will return the #NAME? error value if the Online Analytical Processing server is not available.

Step3

Interpret the error values that may be returned by CUBEMEMBERPROPERTY. The #N/A error value will be returned if there is no member in the cube that matches member_expression or if the syntax of member_expression is incorrect.

Step4

Study the potential problems of referencing a session-based object such as a PivotTable with CUBEMEMBERPROPERTY when sharing a connection. The PivotTable may be deleted or converted into formulas, causing CUBEMEMBERPROPERTY to return the #N/A error value.

Step5

Look at some examples of CUBEMEMBERPROPERTY. =CUBEMEMBERPROPERTY(“Sales”,[Time].[Fiscal].[2007],$A$3) would return the property contained in cell A3 for the member containing the data for fiscal 2007 from the Sales data source. =CUBEMEMBERPROPERTY("Sales","[Store].[MyFavoriteStore]","[Store].[Store Name].[Store Sqft]") would return the value of the property “Store Sqft” from the member “MyFavoriteStore” within the “Store” hierarchy of the “Sales” data source.