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.
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.