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

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.