Monday, August 16, 2010

User Query - Display Totals for Top 2 Ranks while displaying data for all other ranks in a crosstab?

This is in response to a user query.

Requirement: Display Ranks across Columns in a Crosstab and the Total column needs to include only the Top 2 ranks along with Overall Total column for all Ranks.


Step 1: Create the base query Query 1 with the following data items: Product, Year, Sales, Rnk, Top 2 Sales Total.

Rnk - Rank([Sales] for [Product])
Top 3 Sales Total - case when [Rnk] < 3 then [Sales] end

Step 2: Create the outer query Query 2 by referencing the base query Query 1. Drag the data items - Product, Year, Sales, Rnk, Top 2 Sales Total from the referenced query.

Step 3: Set the aggregate function of Rnk to None.

Step 4: Create the crosstab by dragging in the data items Product, Sales, Top 2 Sales Total from Query 2.

Step 5: Create the Overall Total column by clicking on the Summary button for Rank column.



quintessence said...

Zephyr - Firstly, I really Appreciate the time put in by you to resolve my query. Thanks !

However, the above solution doesn't solve my problem. The case statement used by you doesn't have a default else clause. And the dummy data u have used has only 3 ranks so the total would come as top- 3 summary itself.
My question is suppose you had ranks 4 and 5 as well and the values would have been Rank 4-(1000,2000,3000)and Rank 5-(2000,3000,4000) for products A,B,C respectively.
I need two columns -
i) A top 3 summary(same numbers as shown by you) along with the
ii) Overall total which shall the following numbers - (59400, 14075, 41500) for A,B,C respectively ??

Zephyr said...

I have now updated the article to include the scenario mentioned and have included more data to present the same. Let me know if this helps.