3

I know that you can quey a cube by connecting from excel to Analysis database and using formulas like cubevalue() or cubemember(). I also know that after converting the power pivot to formulas you can access the attribute and the value related only by writing a text.

For example: for Branch dimension, instead writing cubemember("connections";"[DimBranch].[Name].[All].[London])", you can write in the cell only "London". However, this won't work if you have a parent-child dimension and want to retrieve the amount for one of the intermediate levels.

Does anyone know how I can avoid writing these formulas directly by the end-user? Can I pass the text "London" as a parameter?

Michael Green
  • 25,255
  • 13
  • 54
  • 100
Pirvu Georgian
  • 131
  • 5

1 Answers1

2

You can put your cube formula in a separate cell and reference the cell where users type in the name they want. For example, if you have users put the branch name in cell B2, you can put this formula in another cell. 

=cubemember("Connection","[DimBranch].[Name].[All].[" & B2 & "])"
mmarie
  • 844
  • 7
  • 19