Hello all. Wondering what I may be missing in this situation. I have a crosstab in release 11.1.7 with multiple nested columns (main parent columns are Month, YTD, TY). The catch is for Month and YTD it is required to show prior month & prior month YTD up until December, then of course show Month & YTD when December arrives. Rather than manually remove the 'prevMember' in the logic every year I hoped to make it dynamic through a Case or IF/Then statement. It was easy enough, however the parent crosstab node members only return the Data Item Names instead of the member caption or data item value. I've changed the TEXT SOURCE source type property to no avail (Data item value, Member caption, Data item label). They all return the same thing, which is the data item name. It works as expected without the case statement, which does have a caption in it to determine the Month. I'm assuming that's the cause? If it helps, here is the before and after logic:
BEFORE (Shows prior Month only, needs to change, looks for the attribute on periods DIM):
prevMember ( item ( filter ( [CC Expense Reporting].[Periods].[Periods].[Months], [CC Expense Reporting].[Periods].[Periods].[Months].[Current Working Period] = 'Yes' ), 0 ) )
AFTER (Shows prior Month only until December, then shows December, however the crosstab node only returns data item name instead of say "Dec-20"):
Case
When ( caption ( item ( filter ( [CC Expense Reporting].[Periods].[Periods].[Months], [CC Expense Reporting].[Periods].[Periods].[Months].[Current Working Period] = 'Yes' ), 0 ) ) contains 'Dec' ) Then ( item ( filter ( [CC Expense Reporting].[Periods].[Periods].[Months], [CC Expense Reporting].[Periods].[Periods].[Months].[Current Working Period] = 'Yes' ), 0 ) )
Else ( prevMember ( item ( filter ( [CC Expense Reporting].[Periods].[Periods].[Months], [CC Expense Reporting].[Periods].[Periods].[Months].[Current Working Period] = 'Yes' ), 0 ) ) )
End
Appreciate any thoughts!