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.
Popular Posts
Tuesday, March 23, 2010
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment