Hello! I have a crosstab report where I want to display "column A" and "column B".
Column A is derived from user selection from a tree prompt.
So code looks something like this: set (#promptmany ('CostCat','MUN','[Cost Breakdowns].[Cost Categories].[Cost Categories].[Total Cost Categories]->:[TM].[Cost Categories].[Cost Categories].[@MEMBER].[Grand Total]')#)
Column B:
descendants(currentMember(hierarchy([Cost Category Level 1])),1)
This works fine if user selects anything but the lowest level. When they choose the lowest level in the tree prompt, I get no data found because there are no descendants to display. If user selects lowest level, I basically want to just display that level in Column B. So Column A and B should be the same.
How can I implement this? Thank you in advance.
Interesting issue. The descendants function doesn't seem to work well with dynamic values, so the expected
descendants(
set()
, if(roleValue('_levelNumber',currentMember()= 4) then (0) else (1)
)
doesn't work.
You can't return sets in an IF statement, so if(levelNumber=4) then (set()) else (set()) won't work.
The only solution I've found is a bit clunky:
union(
children(currentMember([sales_and_marketing].[Time].[Time]))
,head(
currentMember([sales_and_marketing].[Time].[Time])
,if(count(1 within set children(currentMember([sales_and_marketing].[Time].[Time])))=0)
then (1)
else (0)
)
)
)
It takes the children of the current member, and unions either the currentMember if there are no children, or an empty set if there are.
Thank You Paul!!!!
This worked quite nicely. It's a bummer you can't use if then statements for sets. You are right, descendants function does not work very well dynamically.