Crosstab Measures for different months

[CognosAdmn]

Hello All,

I need to build a crosstab report where the rows are product and product type and the columns are actual, budget and variance.

The reports needs to display the measure actual for current month, current month - 1 and current month -1. But budget and variance only for current month.

I do have a dropdown for the month key (YYYYMM).

Currently the report displays all measures for current month. But I need to include the actuals for previous two months.

Any suggestions to resolve this issue will be greatly appreciated.

PS. I did create a second query just for the previous two months actuals piece and join it with the first one.
my filter in the first one is: [MonthKey] = ?p_MonthKey?

I can't come up with the correct filter in the second query.
Since my MonthKey is an integer value, I am not able to put: [MonthKey] = (?p_MonthKey? -2) This wouldn't work when the dropdown is chosen for January (YYYY01) or February (YYYY02)  Because --> 201201 -2=201199  :(

filter in second query (which throws an error):

If (substring(?p_MonthKey?,5,2) = '01')
then ([MonthKey] >= (substring(?p_MonthKey?,1,4) - 1) + '11' and [MonthKey] <?p_MonthKey?)
else if ( substring(?p_MonthKey?,5,2) = '02')
then ([MonthKey] >= (substring(?p_MonthKey?,1,4) - 1) + '12' and [MonthKey] <?p_MonthKey?)
else ([MonthKey] = ?p_MonthKey?-2)

Thank you,


Shaam  :)

[manishbhatt]

Extract current year (from current/sysdate) and then do the calculation

[blom0344]

Code Select Expand


If (substring(?p_MonthKey?,5,2) = '01')
then ([MonthKey] >=
(_round(?p_MonthKey?/100,0)-1)*100+11 and [MonthKey] <?p_MonthKey?)
else if ( substring(?p_MonthKey?,5,2) = '02')
then ([MonthKey] >=
(_round(?p_MonthKey?/100,0)-1)*100+12 and [MonthKey] <?p_MonthKey?)
else ([MonthKey] = ?p_MonthKey?-2)