COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: gosoccer on 23 May 2017 10:57:38 AM

Title: Adding a Column to an existing Crosstab in Relational Model (Report Studio)
Post by: gosoccer on 23 May 2017 10:57:38 AM
Ok, I got a great Crosstab running based on different conditions in the case statement such as below,

case [X_Query].[CAR_BRAND]
when 1  then 'BMW'
when 2 then 'BENZ'
when 3 then 'PAYKAN'

Now, I need to add a condition to this that would be like,

case [X_Query].[CAR_BRAND]
when 1  then 'BMW'
when 2 then 'BENZ'
when 3 then 'PAYKAN'
when 3,4,5,6 then 'ALL CARS'
else 'Others'
END

So, this way the Crosstab will show one more category for ALL CARS and therefore the Crosstab will show the count for ALL CARS.

If the above adding of condition for ALL CARS can not work, could you please guide me as to how I could create a Data Item that would do the count for All CARS so I can drop the Data Item as a part of the existing Crosstab.

Thanks so much for your time.


Please help if you can.  :) :)


Title: Re: Adding a Column to an existing Crosstab in Relational Model (Report Studio)
Post by: Drisya on 25 May 2017 11:18:18 PM
Hi,

Since you have overlapping data in your case statement this will not work.
3 comes in 'PAYKAN' and 'ALL CARS'.

I suggest you create another data item for 'ALL CARS' like

case [X_Query].[CAR_BRAND]
when 3,4,5,6 then 'ALL CARS'
end

Add this new item in your crosstab below the first one.
Hope this helps.

Drisya
https://cognostips.wordpress.com/