Question
Conn Lewis · Jun 15, 2016

How to calculate a dimension percentage on a relationship cube using calculated members?

I am trying to calculate a dimension percentage for WFRole on a child cube named MbMRouteHistoryInitiatorObj joined to a parent cube named MbmQaSObj by using a calculated member dimension within MbmQaSObj. 

If I use the calculated member dimension value expression of [WFRole].[H1].[WFRole].CurrentMember/ [WFRole].[All WFRole].%ALL in the child cube MbMRouteHistoryInitiatorObj, the percentages work correctly.

I am not able to calculate the same percentage using the value expression [MbmRouteHistoryInitiatorObj].[WFRole].[H1].[WFRole].CurrentMember/ [MbmRouteHistoryInitiatorObj].[WFRole].[All WFRole].%ALLin the parent cube MbmQaSObj.  The %ALL gives an error.  Any ideas on how to make this work?

 

0
0 408
Discussion (6)2
Log in or sign up to continue

I receive a zen exception error.  "A JavaScript exception was caught in a function URIError: The URI to be decoded is not valid encoding"  If I remove %ALL the error does not occur. 

Hello Conn,

Would you like me to open a new WRC issue for you, so that one of our DeepSee Support specialist can directly work with you on that?

We can publish the conclusion here once available.

Sincerely,

Asaf Sinay

Hello Conn,

Would you like me to open a new WRC issue for you, so that one of our DeepSee Support specialist can directly work with you on that?

We can publish the conclusion here once available.

Sincerely,

Asaf Sinay

Yes please, that would be awesome!  Thank you for your help.

Conn and I reviewed the documentation page that goes over creating a calculated member for a percentage:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2MODEL_ch_calc#D2MODEL_calc _meas_recipes_percentages

We also looked into using creating a calculated member based on %MDX that would run a select on the child cube and display the results in a pivot table based on the parent cube.  For example, this %MDX based on the related cubes in the SAMPLES namespace does just that:

%MDX("WITH %PARM Group As 'value:' SELECT [AgeD].[H1].[Age Bucket].&[@GROUP] on 1 FROM [RelatedCubes/Patients]","Group",[RelatedCubes/Patients].[AgeD].[H1].[Age Bucket].CurrentMember.Properties("KEY")) / %MDX("SELECT FROM [RelatedCubes/Patients]")

However, rather than displaying data from two different cubes in a single pivot table using %MDX, we decided that the best option would be to create a dashboard that shows the same information but in multiple widgets.  Then we wouldn't need to worry about combining results from different cubes and the pivot tables would be easier to interpret.