Hi , I have a base report where i pass my member unique name for the month selected .
In my drill through report i should have 2 columns , one the selected month sales and other column as sales of selected month -12 months
something like this
Nov 2020 Nov 2019
Product1 100 50
I have the calculation for my dataitem in the column as
dataitem1 - Tuple([H0 Sales],[PrmMonth])
dataitem2 - Tuple([H0 Sales],lag([PrmMonth],12))
[PrmMonth] = #prompt('PrmMonth','mun','[YTD]')#
The problem i am having is I am not able to show the caption of columns as Nov 2020 and Nov 2019 .
It shows the column name as dataitem1 and dataitem2
I tried doing the report expression on the column name , and tried to use
caption(#prompt('PrmMonth','mun','[YTD]')#)
but this caption function will not work in report layout .
SO i directly tried to use substring([Query1].[PrmMonth],1,6)
but this will give me a blank value.
Any sugestions on how to achiee this in here with cubes.
Thanks,
Making the measure to be on crosstab junction and just have the months on column and report layout with Query1.month fixed the issue.