Creating Sum for a Group with Recursion in SSRS

December 1, 2008


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”:

Sum Scope Recursive 1   

I gave the Parent Group the value of the ParentEmployeeKey and the EmployeeKey as the basic value to group on:  Sum Scope Recursive 2   

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:


 Sum Scope Recursive 3  

Which all in all gives us:

 Sum Scope Recursive 4  

 


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:

 Sum Scope Recursive 5 

You define the grouping for the EmployeeKey in the general properties of the details group:


 


Sum Scope Recursive 6



And the rest in the advanced tab for the recursive parent:


 


Sum Scope Recursive 7



And for the group visibility I defined:


 


Sum Scope Recursive 8


Also I had defined that the Indent property of the Full Name textbox will be again: =level()*20 & “pt”
 
The result obviously looks similar:

Sum Scope Recursive 11



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

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

36 comments

  1. AMZFebruary 16, 2009 ב 12:48

    can put the sum in the bottom of the group insted of the top

    Reply
  2. Ella MaschiachFebruary 17, 2009 ב 9:34

    Hi,

    Well if we’re speaking in general, then I would say that you can add a total to a group before or after it in SSRS 2008. In SSRS 2005 I only saw the ability to add subtotals after the group.

    Hope that helped,
    Ella

    Reply
  3. AMZFebruary 17, 2009 ב 11:14

    Thanks for replay,
    but I mean in the recursive grouping not normal grouping, because it seems that recursive grouping is forced on certain order
    I need to make a recursive group that shows deepest levels in the group before the top level.

    Reply
  4. Ella MaschiachMarch 11, 2009 ב 16:24

    Hi,

    Sorry for the late reply, I was sure I had already answered before…
    In any case, as you are opening your parent to show his children (and not the other way around), I don’t think it’s possible for you to show the sum for a group at the bottom and not the top.

    All the best,
    Ella

    Reply
  5. Anup NairMay 26, 2009 ב 6:56

    Hi

    Thanks for the explaining recurssion in reports so beautifully…It was very helpful…. But I have a small doubt suppose we have two table in a report having amount defined in one column,with the same recurssion behaviour. Is it possbile that we can do some calculations on the parent group amount that we get in both the tables and display it in a textbox or table…..

    Thankyou…..

    Reply
  6. Ella MaschiachMay 27, 2009 ב 11:31

    Hi Anup,

    I’m terribly sorry, but I admit I didn’t manage to fully follow your question. In any case, I don’t see how we could show recursion in a textbox and not in a table.

    All the best,
    Ella

    Reply
  7. KamilJune 25, 2009 ב 22:05

    Thank you Ella, you solved my problem

    Reply
  8. tarunJuly 14, 2009 ב 15:16

    It’s a gud artical but i m facing problem to implement this technique in my report.so plz send me the steps if possible.
    thnx in advance

    Reply
  9. AlexanderAugust 25, 2009 ב 13:35

    Hi Ella,

    your explanation for SSRS 2008 worked great. Thank you! I have only a (probably) simple design question:

    Is it possible to move toggle buttons (those with + and -) to any other position then the one on far left?

    Thanks,
    Alexander

    Reply
  10. Ella MaschiachAugust 25, 2009 ב 14:10

    Hi Alexander,

    I admit I don’t know that you can move the position of the toggle buttons through the UI for editing the group properties.
    Perhaps (I’m not sure), you can do so through Report > Report Properties > Code Tab, if you’re interested in writing code for the subject.

    All the best,
    Ella

    Reply
  11. JasonAugust 27, 2009 ב 20:59

    I’m trying to sum the grouped total in a tablix. Can you tell me how that is done? So for in your example, how would you sum the Total_Order_Quantity column in the tablix footer?

    Thanks~

    Reply
  12. Ella MaschiachAugust 30, 2009 ב 14:36

    Hi Jason,

    If you’d like to add a Total after all the rows, right click the detail part of that column,  chose “Add Total” > “After”. That should do the job for you. You can check a webcast I did on how to build a basic report in Report Builder 2.0 .

    Hope that helped,
    Ella

    Reply
  13. JasonAugust 31, 2009 ב 16:21

    Thanks for the tip and great webcast. I think my problem may be a group scope issue. I’m trying to get the sum of a groups value, which is not a sum of the column of the inner group. It’ just a value that is on every detail record called Total_Charges.

    Total_Charges comes back with every detail record and I have two groups leading down to the details. I can show that single value in the total row of the first group, but for some reason it adds all the Total_Charges for the top group. So it’s adding it way too many times. I’ll keep messing with it, I’m sure I’ll get it.

    Reply
  14. Ella MaschiachSeptember 1, 2009 ב 15:58

    Sorry I couldn’t help Jason…< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />Last try though – maybe it’s a matter of a recursive sum in a matrix?

    I’m sure we’ll all be happy to hear your end solution, so please come visit us again.

    Reply
  15. Matt BoozerOctober 28, 2009 ב 22:52

    This is great. But, What if I wanted to include the details row where the visibility could be toggled to reveal the individual records that make up the sum? Is that possible?

    Reply
  16. Ella MaschiachNovember 4, 2009 ב 14:31

    Hi Matt,

    I did try to do the same thing with a recursive sum and also detailed information which built that number, but didn’t succeed much. I have to admit, it also didn’t appeal to me as it made the report too detailed and long. Maybe instead you should give your users the report as I have described it above, and if they want the detail rows, you can send them with a click to a subreport which details the workers sales that made up the totals.

    Hope that helped.

    All the best,

    Ella

    Reply
  17. AminJanuary 12, 2010 ב 14:33

    Hi Ella;

    Thanks for the greate post.
    but i was wondering if i can aggregate Stephan orders and all of Stephen children orders.

    this way you’ve posted only shows Stephen ordery himself. not with the sub-employees.

    thanks a lot
    Amin

    Reply
  18. Ella MaschiachJanuary 14, 2010 ב 10:50

    Hi Amin,

    If you review the post, you’ll see that though “Order Quantity Per Employee” gives just the total per employee, “Order Quantity Total” gives the total for the worker and all the employees that work under him.

    All the best,
    Ella

    Reply
  19. AminJanuary 19, 2010 ב 13:23

    Hi Ella

    Thanks for reply.

    I dont know what is the expression for “Order Quantity Total”. could you please tell me what to do?

    And may I have the whole Hierarchy from root to end?
    We have only employees that have order themselves.

    For Example:

    -Root 0 1,000,000
    -Employee1 0 100,000
    +Employee1_1 1000 90,000
    +Employee1_2 2000 10,000
    -Employee2 500 1,500,000
    -Employee2_1 1000 1,000,000
    +Employee2_11 500,000 900,000

    and so on…

    Reply
  20. Ella MaschiachJanuary 20, 2010 ב 13:18

    Hi Amin,

    I’ve attached my SSRS 2005 Recursive Sum Report.
    Create a Reporting Services Project in the BIDS and give it a data source that connects to the Adventure Works DW DB.
    Download my file an add it to the SSRS project. Give it the data source you created and then give the groups and formulas (Order Quantity Total) a look, according to what I wrote in the post.
    Hope that helps.

    All the best,
    Ella

    Reply
  21. KrishnaMay 20, 2010 ב 16:01

    Hi Ella,

    My requirement is to show the some of the data as normal and remaining data in drill down.

    Example: I have a list of 50 suppliers and i have to show first 10 suppliers as normal and for remaining 40 supplier i have to show under drill down.

    Please help

    Regards,
    Krishna

    Reply
  22. Ella MaschiachMay 23, 2010 ב 8:31

    Hi Krishna,

    I’m terribly sorry, but I don’t know of a way to do that in SSRS 2005.

    Ella

    Reply
  23. Matthew WarmanSeptember 14, 2010 ב 12:01

    Hi,

    If you have already answered this question then i applogies.

    I have created a report with a recursive group using a locations with a sub group which is an asset.

    When the sub group is defined the recursive totals for location do not display, when the sub group is removed they display.

    I was wondering (hoping) you have any thoughts or input.

    Regards
    Matt

    Reply
  24. Ella MaschiachSeptember 16, 2010 ב 11:10

    Hi Matthew,

    Recursion can only work on one attribute which is summed within itself.
    For recursion to work, there should be only one parent child dimension with locations connected assets (in one attribute in one table).
    If you’d like to have two nested totals for two different attributes, use the usual subtotal.

    Hope that helped,
    Ella

    Reply
  25. djeffOctober 20, 2010 ב 16:15

    I have clints and parent clients, for every client there is an amount, I defined a group based on parentclient. I want to display groups sorted by total and clients in the group sorted as well by amount. it worked in rs2000: sort expresion containing sum recursive. but it does not work in rs2005.

    Reply
  26. z0mbieJuly 22, 2011 ב 7:26

    Hi Ella,

    Could you please check above link and I think you could help me to do that.

    Thanks.

    Best Regards,
    zombie

    Reply
  27. Ella MaschiachJuly 24, 2011 ב 15:17

    I tried to check the question, but didn’t understnd your code.

    Reply
  28. SimonSeptember 20, 2011 ב 22:40

    Hi Ella,

    Good Post. I am running a problem here.
    I want to add a sub group “Products” under the Sales.
    But I couldn’t get the sum on sales and sale manager

    For Example:

    -Sale Manager1 3000 100,000

    -Sales1 1000 90,000

    -Sales2 2000 10,000

    – Products1 1000 5,000
    – Products2 500 5,000

    As you see the I add a sub group under the Sales/Sales Manager Recursive Group. But I couldn’t get the total $ in Manager level.

    Could you give me some advices

    Reply
  29. Ella MaschiachOctober 16, 2011 ב 15:40

    Hi Simon,

    Did you define the parent of Product1 and Product2 to be Sales Manager1?
    Otherwise, I can’t see the problem.

    Hope that helped,
    Ella

    Reply
  30. SimonNovember 25, 2011 ב 1:22

    Hi Ella,

    Sorry, I was on vacation last month.

    No. the products only relate to Sales. You can think it is a sub group of sales.

    Thanks,
    Simon

    Reply
  31. Ella MaschiachNovember 27, 2011 ב 15:12

    Hi Simon,

    I’m not completely following you. Try posting your question on:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads

    All the best,
    Ella

    Reply
  32. Ella MaschiachNovember 27, 2011 ב 15:40

    Simon,

    I’m not following you. try posting a question on:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads

    All the best,
    Ella

    Reply
  33. AdamJanuary 19, 2012 ב 16:27

    Great article, very helpful for an SSRS n00b :)

    One question: how can I get a recursive sum that only counts the children but not the parent?

    You showed above how to get the sum for a manager AND all of those working under him, but how can I get it for only those working under him? Would I need to do some sort of subtotal?

    Thanks!

    Reply
  34. Ella MaschiachFebruary 2, 2012 ב 12:02

    Hi Adam,

    I tried to think about it, if you can use perhaps “scope” to define what you want. Unfortunately enough, I don’t think you can create a sum just for the workers level. RS can only identify a jump in the level (and then a different sort of sum) when it identifies a parent. This is the only sort of sum available for this scenario. If you don’t want to use recursion and just a regular grouping, you need some sort of flag to show the change in the level of the group.

    All the best,
    Ella

    Reply