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?

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 & "])"  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.