I have DMR report with Tree prompt at Crosstab and hierarchy of prompt is like Location(All) Under Location(All) we have US and CN.Further levels havd US regions and CN regions as well well the Issue is with below scenario
1)User selects Location(All) and United states then expected report output is it should display Total measures for Location(All) only but actual result is
report displays Total measures of Location(All) and United states i.e. Report is adding up measures of Location(All) and United states
Below is my DMR expression for Total Data Item
aggregate(currentMeasure within set set ( #promptmany ('p_Location', 'memberuniquename', '[Hierarchical].[Company].[Location].[Company (All)]->[all]')#))
posted full report to make my problem more clearer
Also do let me know if i need to send package hierarchy
Thanks you group ,i got solution for this ,just sharing
Achieved using String variable with 3 values Location_All US and CN
Then created 3 data items with expressions
aggregate(currentMeasure within set [CANADA])
aggregate(currentMeasure within set [UNITED STATES])
aggregate(currentMeasure within set [Company (All)])
next call that string variable in style variable and set Box type as None for US and CN when Location (All) string value selected,next set Box type as None
for Location (All) and [CANADA] when US string value selected and finally set Box type as None for Location (All) and [CANADA] when CN string value selected.
In variables section of String variable which will be used further used as Style variable,write below Case statement
CASE
WHEN ParamDisplayValue('p_Location') IN ( 'Location (All)')
THEN
'Location_All'
WHEN ParamDisplayValue('p_Location') NOT IN ( 'Location (All)')
AND
ParamDisplayValue('p_Location') IN ( 'UNITED STATES')
THEN
'US'
WHEN ParamDisplayValue('p_Location') NOT IN ( 'Location (All)')
AND
ParamDisplayValue('p_Location') IN ( 'CANADA')
THEN
'CN'
ELSE
'Location_All'
END