Wednesday, August 11, 2010

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:





 

3 comments:

Zephyr said...

Scott, let me know if the above images look any similar to your requirement.

Scott said...

Not quite, Zephyr. If there is some way I can e-mail you exactly what I have been tasked to provide, I will do that.

BTW, I have been reading through your other entries out here and there is a lot of terrific content. I have bookmarked this page and will probably use it as a significant resource. Thanks for all you do.

Al in SoCal said...

Can you write out the exact formula here or post a screenshot - it would help me out a great deal:


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

Thanks!

Al in SoCal