Use aggregates of aggregates in Reporting Services 2008 R2

By | February 24, 2010

I recently had a question concerning reporting services, they wanted to compare sales of a month to the average sales per month like in the report i reproduced in the report below:

This is not easy in reporting services 2008 and before. To solve this problem we need to divide the total sales by the number of months, which you can obtain by using the CountRows function. But then you have a problem with the country’s. It would be solvable, but using a lot of tricks.

Suddenly i remembered a blog post from Robert Bruckner which mentions the new features of SQL Server 2008 R2:

Aggregates of Aggregates
This enables report authors to nest RDL aggregate expressions inside other RDL aggregate expressions with unlimited nesting levels.  For example, the expression =Avg(Sum(Sales, “Month”), “Year”) would compute the average total monthly sales

This could solve my problem very easy using the new expression:

=avg(Sum(Fields!Internet_Sales_Amount.Value),”Calendar_Year”)

This will give us the avg of the total sum of sales per month per year. We then compare it to each other and determine the percentage.

Using the aggregates of aggregates functions could make my life a lot easier in my daily work, just like the Lookup function i talked about before.