Integrating MDX: MS Excel's CUBEMEMBERPROPERTY function

• john.hartnett

  Old Hand

  Points: 327

  More actions

  August 21, 2013 at 3:48 pm

  #278927

  I've recently discovered MS Excel's CUBE Functions, which are really tremendous. The syntax on CUBEMEMBERPROPERTY isn't well documented, and always get a #N/A error, despite success with CUBEMEMBER, CUBEVALUE, and even a CUBESET and CUBERANKEDMEMBER combo (really cool).

  I'm trying to retrieve an attribute for an account, the syntax I'm trying is:

  =CUBEMEMBERPROPERTY("Cube_name","[Account].[Account ID].&[37206]","[Account].[Business Purpose]")

  which returns "#N/A"

  Where the result should be "Investment Management". I've converted a pivot table looking at the SSAS OLAP into formulas, and got the following result for the property:

  =CUBEMEMBER("Cube_name",{"[Account].[Account ID].&[37206]","[Account].[Business Purpose].&[Investment Management]"})

  The CUBEMEMBER function is unhelpful because I'm required to know the Business Purpose of "Investment Management" in order to get the value returned.

  Does anyone have insight into the MDX syntax rules for this Excel function?