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.

Solution:

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.




 

User Query - Creating Overall Percentage Summary Values in Reports

This requirement is in response to a user query:

Requirement: Display Dates, Sales, Percentage Sales along with summary total.

Solution:

Step 1: Create a crosstab report with Year, Month, Sales data items.

Step 2: Create a data item Total Sales - total(...[Sales] for report).

Step 3: Set the Aggregate and Rollup Aggregate functions to calculated.

Step 4: Create a data item Percentage - [Sales] / [Total Sales] and set aggregate and rollup to calculated. Drag this query item into the crosstab.



Here are a couple of other ways to format the above information: