Creating Sum for a Group with Recursion in SSRS - Ella Maschiach's BI Blog

# Ella Maschiach's BI Blog

BI, Olap Technologies, Tools, System Analysis and Design

# 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

#### AMZ said:

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

# February 16, 2009 12:48 PM

#### Ella Maschiach said:

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

# February 17, 2009 9:34 AM

#### AMZ said:

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.

# February 17, 2009 11:14 AM

#### Ella Maschiach said:

Hi,

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

# March 11, 2009 4:24 PM

#### Ella Maschiach's BI Blog said:

Recursive Sum for a Group in a Matrix in SSRS I wrote a post not too long ago about creating Sum for

# April 22, 2009 9:27 AM

#### Anup Nair said:

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.....

# May 26, 2009 6:56 AM

#### Ella Maschiach said:

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

# May 27, 2009 11:31 AM

#### Kamil said:

Thank you Ella, you solved my problem

# June 25, 2009 10:05 PM

#### Ella Maschiach said:

# June 29, 2009 1:14 PM

#### tarun said:

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.

# July 14, 2009 3:16 PM

#### Alexander said:

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

# August 25, 2009 1:35 PM

#### Ella Maschiach said:

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

# August 25, 2009 2:10 PM

#### Jason said:

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~

# August 27, 2009 8:59 PM

#### Ella Maschiach said:

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

# August 30, 2009 2:36 PM

#### Jason said:

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.

# August 31, 2009 4:21 PM

#### Ella Maschiach said:

Sorry I couldn’t help Jason…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.

# September 1, 2009 3:58 PM

#### Matt Boozer said:

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?

# October 28, 2009 10:52 PM

#### Ella Maschiach said:

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

# November 4, 2009 2:31 PM

#### Amin said:

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

# January 12, 2010 2:33 PM

#### Ella Maschiach said:

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

# January 14, 2010 10:50 AM

#### Amin said:

Hi Ella

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...

# January 19, 2010 1:23 PM

#### Ella Maschiach said:

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

# January 20, 2010 1:18 PM

#### Krishna said:

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.

Regards,

Krishna

# May 20, 2010 4:01 PM

#### Ella Maschiach said:

Hi Krishna,

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

Ella

# May 23, 2010 8:31 AM

#### Creating Sum for a Group with Recursion in SSRS « A Blog for SQL Server Reporting Services (SSRS) Programmers said:

Pingback from  Creating Sum for a Group with Recursion in SSRS &laquo; A Blog for SQL Server Reporting Services (SSRS) Programmers

# June 17, 2010 6:33 AM

#### Matthew Warman said:

Hi,

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

# September 14, 2010 12:01 PM

#### Ella Maschiach said:

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

# September 16, 2010 11:10 AM

#### djeff said:

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.

# October 20, 2010 4:15 PM

#### Ella Maschiach said:

Hi Djeff,

I've answered you under the same comment you posted at blogs.microsoft.co.il/.../recursive-sum-for-a-group-in-a-matrix-in-ssrs.aspx

All the best,

Ella

# October 25, 2010 1:00 PM

#### z0mbie said:

Hi Ella,

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

Thanks.

Best Regards,

zombie

# July 22, 2011 7:26 AM

#### Ella Maschiach said:

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

# July 24, 2011 3:17 PM

#### Simon said:

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

# September 20, 2011 10:40 PM

#### Ella Maschiach said:

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

# October 16, 2011 3:40 PM

#### Simon said:

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

# November 25, 2011 1:22 AM

#### Ella Maschiach said:

Hi Simon,

I'm not completely following you. Try posting your question on:

All the best,

Ella

# November 27, 2011 3:12 PM

#### Ella Maschiach said:

Simon,

I'm not following you. try posting a question on:

All the best,

Ella

# November 27, 2011 3:40 PM

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!

# January 19, 2012 4:27 PM

#### Ella Maschiach said:

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

# February 2, 2012 12:02 PM