Report Studio : Top or Bottom Selling Products against Dimensional Model

If you have tried creating a report that should display either Top or Bottom 5 products based on Revenue against a dimensional model in Cognos version 8.4, you would have noticed that using a case statement seems to cause validation issue.

The reason for the same has been explained in the article : Case Statement Issues in 8.4 with Dimensional Model

Case Statement Issues in 8.4 with Dimensional Model

This particular issue deals with Coercion errors. Your report works fine in 8.1 and when you have converted it to 8.4.1 you get an error that looks similar to the following: 

QE-DEF-0478 Invalid coercion from 'value' to 'measure' for 'when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then 
[Quantity] ' in 'case when ?Measure? = 'R' then [Revenue] when ?Measure? = 'Q' then [Quantity] end'. 

This is due to the usage of Case statements against dimensional models. Cognos had earlier warned against usage of this against Dimensional Models now they seem to have started restricting the same.

Try using an If Then Else to work around this. But Cognos definitely needs to come up with a similar construct for Dimensional Models as well as most work revolves around the usage of Case statements.

------------------------------------------------------------------------------------------------------------------------------------------

To work around this issue, the steps have been explained below:

Step 1: Create the following 2 Set Expression

TopCount Products - filter(topcount([Cube].[Products].[Products].[Product],5,[Revenue]), ?TopBottom? = 1)

BottomCount Products - filter(bottomcount([Cube].[Products].[Products].[Product],5,[Revenue]), ?TopBottom? = 2)

Where ?TopBottom? is a prompt that accepts as its values - 1 (Top Count) or 2 (Bottom Count)

Step 2: Create the following Union Set Expression

Products - union([TopCount Products],[BottomCount Products])

Step 3: In the list report, drag Products and Revenue measure.

Here if you notice, I have avoided using Case statement as they seem to be unsupported or do not work as expected in 8.4.

The report now works as expected.