Creating Sum for a Group with Recursion in SSRS
Creating Sum for a Group with Recursion in SSRS
Sum with scope
A colleague of mine from work was creating a report with Reporting Services 2005. He had a matrix report like the following:
| |
Year |
Total |
| Total Sales Per Year |
% of Sales from the Total Sale Per Status |
| Status |
|
|
1 |
| Total |
2 |
|
3 |
He wanted to calculate the percent of total for that year from the total for that row. I remembered from looking at the Sum function in SSRS that it had 3 options – with scope and recursion. So I thought – I should try the scope. And indeed, scope enables you to define a sum for either the entire dataset, a data region (like 3) or a group - be it a row group (like 1), a column group (2) or even just a details group if we were talking about a table. What do you have to do? Basically just write something along the lines of:
=Sum(aggregate, "GroupName") The GroupName can be taken with a right click on your data region and then going to the Groups tab (and there also to the Details Grouping if you're using a table).
The entire formula for the needed calculation would be:
= Sum(aggregate) / Sum(aggregate, "GroupName") Because, if otherwise specified, sum will calculates for the cell it's in.
Scope exists, as the MSDN page states, for quite a few functions.
Sum with Recursion
Still, I saw that there's also a third option for Sum, and this one also included a recursive aggregation. Unfortunately enough, MSDN does not address recursion in its documentation for Reporting Services 2005 (though it already exists there), but only for Reporting Services 2008. So I thought maybe I should try and study it more thoroughly and see how it would work out in Reporting Services 2005 and 2008.
Sum Recursive Reporting Services 2005
I created a simple report with a table, based on Adventure Works DW with Employee dimension (which is a Parent Child Dimension). For the measure I'm going to summarize I used OrderQuantity from FactResellerSales. I've put the name of the employee on the details rows. I entered the definition of the table and under groups, entered "DetailsGroup":
I gave the Parent Group the value of the ParentEmployeeKey and the EmployeeKey as the basic value to group on: 
Now, as each employee has his own Order Quantity we can sum for each employee with the scope part of the sum, meaning: =Sum(Fields!OrderQuantity.Value, "table1_Details_Group") For each employee, if we want to see the sum for himself and all of those working under him, we use the recursive part of the sum function: = Sum(Fields!OrderQuantity.Value, "table1_Details_Group", recursive) I have also used right padding of: =level()*20 & "pt"
For the table's Details Group I defined in it's visibility:
Which all in all gives us:
Sum Recursive in Reporting Services 2008
I created the same report in SSRS 2008 with just a few alterations:
In the Tablix, you don't go into the Tablix properties and define the Details group properties for them, as they are kept outside:
You define the grouping for the EmployeeKey in the general properties of the details group:
And the rest in the advanced tab for the recursive parent:
And for the group visibility I defined:
Also I had defined that the Indent property of the Full Name textbox will be again: =level()*20 & "pt" The result obviously looks similar:
And that is how you can use Scope and recursion in Reporting Services functions.
Update January 20th 2010: I’m attaching my report in SSRS 2005 for your viewing pleasure: Sum Recursive