How to show top 10 revenue by product and overall based on Month in Cognos Report Studio using crosstab?
This is what I've done so far:
1. Create a crosstab report
2. Drag [Sales (query)].[Products].[Product] into ROW.
3. Drag [Sales (query)].[Sales fact].[Revenue] into MEASURE.
4. Drag Month into column and filter by 'November' in detail filter.
5. Create new data item for top 10 product based on revenue and drag it to column. Expression formula:
if (rank([Product])<=10) THEN
([Revenue]) ELSE (NULL).
6. create another data item to Calculate TOP 10 percentage and drag it to new data column. Expression Formula:
percentage ([Top 10 Revenue])
7. Create data item for Overall product and drag it to column. Formula expression:
total ([Revenue] for all [Product])
8. Percentage for overall revenue. Expression formula : percentage ([Total Revenue for all product])
9.Create new data item to rank top 10 product. Expression formula . Rank([Product].
10. Under detailed filter, filter Rank:
[Rank] <=10.
while running the report, i notice that the result is not showing the overall result with the rank filter set <=10. If i remove it, it will show overall and top 10 side by side.
Can anyone guide me, how am i able to capture the overall result so i can compare it with my top 10 result?
Regards,
Joe
Hi Joe,
I'm trying to understand your issue, so in your current output is showing top10rev by product is the same as the overall based on Month is this assumation correct? This would make sense if this was coming from on query.
I think you need to create two queries one for the top10rev products and other for overallrev by product then join them to create your output query. Also if you can please take a screenshot of your issue, I think that would help.
Thanks,
RK