Print Page - Year clculation in Crosstab

Title: Year clculation in Crosstab
Post by: S_N_Solution on 07 Aug 2009 03:03:56 AM

Hello,

I want to create a crosstab report in which some mesures have taken as Row.Year as a Column.At last printing the Total of those measures.I want to add some calculation in the column along with year column. like (2009/2008)-1,(2009/2007)-1 in percentage.How can i add those column.
Enclosing the report format for the refrence.Please help ASAP...

Thanks in advance.... :(

Title: Re: Year clculation in Crosstab
Post by: blom0344 on 07 Aug 2009 03:22:34 AM

1. Create dataitem (for each new column in report!) like:

Quote
(total((CASE WHEN [YEAR] = 2009 THEN [VALUE] ELSE 0 END))/
total((CASE WHEN [YEAR] = 2008 THEN [VALUE] ELSE 0 END)))-1

2. Add this to the right of the existing year column

3. Set aggregate property to calculated
4. format it as a percentage

Title: Re: Year clculation in Crosstab
Post by: S_N_Solution on 07 Aug 2009 03:38:41 AM

Hi,

Thanks..this will help me somehow but..

It is giving error as incompatible data type.
Also is it possible to make it dynamic because always the year would not be 2009.This can change.and the calculation should be till min of year.

Title: Re: Year clculation in Crosstab
Post by: blom0344 on 07 Aug 2009 04:15:55 AM

Just tested it, works for me, though only with fixed values for the year. I guess you can make this dynamic if you have a date prompt and take the year from the parameter associated.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: S_N_Solution on 07 Aug 2009 03:03:56 AM