Creating Sum for a Group with Recursion in SSRS
Sum with scopeA colleague of mine from work was creating a report with Reporting Services 2005. He had a matrix report like the following:
|Total Sales Per Year||% of Sales from the Total Sale Per Status|
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 RecursionStill, 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