Hi i have a requirement in a report to use 2 previous months in a crosstab.
I have, in the database, a field called Fiinancial year Month, in format of '201204'.
and i want a calculation to use '201203' and '201202' from '201204'.
Please suggest what functions can be used.
Are you using relational or dimensional model?
If you use a dimensoinal model than you can use the lastPeriods dimensional function like this: lastPeriods(3, [Actual Month]) where [Actual Month] is a calculated member returns [201204] in this case. If you want to filter the actual month from the result set than you can achieve this with embedding the lastperiod function into a head function.
If you use relational model than you have to combine the cast, substring and _add_month cognos functions, or use the native functions of your DB provider.
I am using relational model... pelase suggest how to make that calculation
which DB provider do u use?
ellipse
I haven't got any experience with ellipse, so I don't know which DB functions to use.
I created a demo report from the "GO Sales (query)" package existings in Cognos samples. I used only cognos functions, so it should work in every enviroment.
Let you have a look, you have to create something like this.
I don't know Ellipse either, but I suspect the CAST() function should work.
Dataitem One month back:
cast(if ([Financial Year Month] ends with '01') then (cast([Financial Year Month]; smallint)-89) else (cast([Financial Year Month]; smallint)-1); char(6))
Dataitem Two months back:
cast(if ([Financial Year Month] ends with '01' or [Financial Year Month] ends with '02') then (cast([Financial Year Month]; smallint)-90) else (cast([Financial Year Month]; smallint)-2); char(6))