Dimensional reporting

[cognos05]

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,

[cognos05]

Making the measure to be on crosstab junction and just have the months on column and report layout with Query1.month fixed the issue.