# Recursive Sum for a Group in a Matrix in SSRS

I wrote a post not too long ago about**creating Sum for a Group with Recursion in SSRS**. A reader of that post dropped me a line as she was having trouble recreating the same behavior in a matrix (the post itself had demonstrations in it only for a table). So I decided to look into how a recursive sum acts in a matrix, and boy what a learning experience that turned out to be…

I created a report with a matrix which connected to Adventure Works DW. I defined the employees as the group on the rows (the recursive group with the parent – child relationship), and the product line as the group on the columns. I added styling to the

**padding**and

**headers in SSRS**as I have demonstrated in previous posts.

**InScope**function in SSRS, and helped me play around with it. The function helps you see where you are in respect to a dataset, a group, or a data region. Using the InScope function in a matrix can help us manipulate the sum that’s created for each part of the matrix (the inner cells, the total for the columns, the total for the rows or the total for the matrix). In a brilliant post by

**Jorg Klein**, you get a very elegant and powerful solution to how to break your matrix into all of its parts. Using Mr. Klein’s very smart expression, I wrote:

=Iif(InScope(“matrix1_ProductLine”),

sum(Fields!SalesAmount.Value),

Iif(InScope(“matrix1_FullName”),

sum(Fields!SalesAmount.Value,“matrix1_FullName”,recursive),

sum(Fields!SalesAmount.Value)))

Which basically means that in the matrix the following happens (see picture with correlating numbers):

- In the cells, we get for each employee the sales he made for that product line. If he’s the boss, we still get only the sales he made (and not his sales with his workers sales)
- The subtotal for the row group is as usual for the scope of product line.
- The recursive sum appears in the subtotal of the column group for the employee.
- The total for the matrix is as usual (a total of the data in the dataset).

sum(Fields!SalesAmount.Value,“matrix1_FullName”,recursive)

**level**zero (of the parent) will get the recursive sum and the rest will get the usual sum. That also didn’t help.

I would also add that though in my previous post about recursive sum I gave you the solution for Reporting Services 2005 and Reporting Services 2008, this time I’m giving you the solution just for SSRS 2005 because my VPC crashed, when my PC crashed….

I’ll try to get you the solution for SSRS 2008 in an upcoming post.

**Sum Recursive Matrix**

I hope this helped you create the recursive sum you need for a group in your matrix.

hi Ella:

can you give a detailed sample to me?I am a fresher to the SSRS that used it to display the parent-child dimensions in matrix.this problem has already puzzled me for many days.thx.

from wing

Hi Wing,

I have already attached the detailed file to the blog post (look at the last paragraph “Sum Recursive Matrix”).

Hope that helps,

Ella

Hi Ella

I am having difficulties understanding this InScope function. I tried copying your code above and using it for navigation on matrix totals but I’m not winning everytime I click on the total it randomly select a cell and pass it as a parameter which then give me incorrect info.

may you please give an example of how to do this

many thanks

Hi Thato,

The formula is not meant for navigation but as an expression in a cell inside the matrix itself. Try to build an SSRS project in your BIDS and then download the RDL file I put at the end of the post, and attach it to the project.

Look at the file and that should explain it the best way.

All the best,

Ella

Exporting the report to Excel does not keep the visual hierarchy. Any ideas / suggestions to keep the + , -?

Thanks for this article.

Hi Sri,

From what I understand, this is a known problem with no workaround:

social.msdn.microsoft.com/…/5fe9d2e3-6e4d-4aac-bc2c-bf4f011e93ef

Also see:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/29576945-a99a-482a-ba4b-a8639ceef31a

All the best,

Ella

Hi Ella,

I’m having a problem with a Tablix in SSRS 2008.

I have:

On rows: 2 levels of hierarchy.

On columns: 2 levels of hierarchy.

Everything is with subtotals. It’s something like this:

Year

M1 M2 M3 Totalyear

Category C1

Subcategory S1

S2

S3

C1Total

Category C2

Subcategory S1

S2

S3

C2Total

Overall Total

What i want to do, is to calculate a percent over the parent group, i.e:

in (S1,M1), i want ((S1,M1) / (C1Total, M1))

the same way, what i want in (C1Total, M1) is to calculate ((C1Total, M1) / (Overall Total, M1)).

So far, i’ve been able to calculate percent over the parent group, but the problem is that it calculates it over the total of the tablix, so it gives me back the same value for each month.

I’ve been reading about this and it sais that you can’t calculate an aggregation function with 2 scopes (category and month, in this case).

I need to find another way to calculate what i want.

The data comes from a MOLAP cube in analysis services, so an alternative is to precalculate it there, but i haven’t been able to do it.

Any help is welcome.

Thx to anyone who answers me. (i’m sorry about my english, but is not my native languaje).

Juan Ignacio

Hi Juan,

Indeed you are right and you cannot calculate an aggregation function with 2 scopes. If you’re willing to use a scope in MDX, you would need something along the lines of:

Create Member CurrentCube.[Measures].[Ratio S1M1 to C1M1]

As IIf

(

IsEmpty

(

[Measures].[Sales], [Category]

),

Null,

(

[Measures].[Sales], [Subcategory]

)

/

(

[Measures].[Sales], [Category]

)

),

Format_String = “Percent” ;

Assuming your regular measure is called [Measures].[Sales Ratio] what you would need to do in MDX is also add a scope to replace the ratio calculated with the one you created yourself:

Scope

(

[Subcategory].Members,

[Date].[Fiscal].[Fiscal Months].Members,

[Measures].[Sales Ratio]

) ;

This = ([Measures].[Ratio S1M1 to C1M1]) ;

End Scope ;

Hope that helped,

Ella

Hi,

I am able to create “A Recursive Hierarchy Group”. My problem is the data appears in this way

– A 8000

A1.1 5000

A1.2 3000

– B 17000

B1.1 8000

B1.2 9000

but I want the report to show the data in the below format

A1.1 5000

A1.2 3000

– A 8000

B1.1 8000

B1.2 9000

– B 17000

Please let me know if this is possible in Recursive hiearacial Groups ?

Thanks

Hi Ravi,

I regret to say that as far as I know that’s not possible.

All the best,

Ella

Hi Ela,

Thanks for your answer,Please let me know if you come accross this.

Bye

Ravi

Hi ella, thanks for your answer, it works just fine!

Glad I could help

Hi,

i have a small query in SSRS, which is quite big for me right now. Actually i hav to calculate the Progressive total in a Table. The situration is like:

Intial(Opening Bal) : 2000

S.No Cost Progressive_Cost

1 100 2100 (Opening Bal + Current Cost)

2 200 2300( Previous Progressive_Cost+cost)

3 300 2600(2300 + 300)

Kindly help me, How can i achieve that.I am using SSRS right now.I have also tried Previous() Function but thats not giving the desired Result

Hi Rohit,

First of all the opening balance should be also part of your dimension. It should look like:

S.No Cost Progressive_Cost

0 2000 2000

1 100 2100

2 200 2300

3 300 2600

This way you can achieve progressive cost with the SSRS function of RunningValue. The function is located under Sommon Functions > Aggregate. For use of the function, please look at the following:

http://msdn.microsoft.com/en-us/library/ms159136.aspx

http://blog.stevienova.com/2007/07/02/ssrs-runningvalue-to-get-cumulative-totals/

All the best,

Ella

does the sum recursive work in the sort expression in rs 2005? I get error.

Hi Djaif,

I don’t think that SSRS can compute the recursive subtotal (whose value is dependant per cell) and sort on it, all before rendering the report. If you want to sort on this expression, you’ll have to calculate all the values before hand in the dataset itself of the report.

All the best,

Ella

with rs2000 I have sortexpression on the group witch contained a sum with recursive parameter

IIF((Parameters!SortDir.Value=”Desc” AND Parameters!Sort.Value=”Revenue”),Sum(FIELDS!RevTotal.Value, “table3_CustomerGroup”, Recursive),0)

So rs did all the job sorting in recursive way every level in the hierarchy :

Parent1 1000

Parent 2 1500

Parent 1-2 700

Enfant1 300

Enfant2 400

Parent2-2 800

Enfant1 200

Enfant2 600

But in rs 2005 this expression results error!! Without the parameter recursive there is no error but the sort does not work on parents

I tried to implement it using the interactive sort new feature in rs2005. There is no error for the sort expression with recursive parameter but the sort does not work properly.

Clients families are grouped sing a key CustomerId, ParentCustomerId.

Thank you Daniele for the added information on Djaif’s question!

no its not add information!!! I have exactly the same problem as Djaif. I dont know how to make it work using just features of RS2005!!!?

hi,

thanks for your solution!

i have also 1 problem about sum over colums.

column1 column2

50 30

100 60

sum col1: 150 sum col1:90

Grand Total = sum col1 + sum col2

i try to calculate this Grand total but RS 2008R2 don’t want to summe the values. Please can you help me?

Ida

Daniele – as I have told Djaif before, I’m afraid I cannot assit you on the matter.

Ida – try creating another column which has an expression to sum column 1 + 2.

All the best,

Ella

Hi Ella,

Sorry if it is a repeat, from the figure, What is the process or expression to add column “ “T and Column “ “S” Assuming it is matrix report and the columns are displayed based on the selection in the parameters. Say for example your param A (You selected “R,M,S,T”) every time your selection changes, but you always need to sum the last 2 columns.

Any suggestions are highly appreciated.

Thanks,

Devi

Daniele and Djaif – please look at the following link:

http://technet.microsoft.com/en-us/library/dd255193.aspx

Follow the steps described for “To sort values in a group, including the details group, for a Tablix”. You should plant your expression in the sort expression for the group and that should work well enough.

Devi – just to make sure, are you asking how to sum the last two columns, never mind which 2 columns were chosen by the user?

Hi Ella,

I have an issue with SSRS 2008(not sure if its an issue). Our requirement is to have a recursive hierarchical report which i was able to crack through. However we also have a column grouping and when the recursive sum (rolling up sub total) is used, the values are being doubled.

Month1 Month2 Month3

Level 1 3 2 1

Level 2 2 0 0

Level 3 1 2 1

As per the above, I need to display hierarchical information month wise along with the subtotals rolling up. However recursive SUM double up the values.

Is there a way out to display accordingly. All the help is much appreciated. Thanks.

hi, thanks for the post. it really helped me a lot. just a quick question, i tried the solution below but it seems it’s only showing 2 levels? I have a data that drill down upto 5 levels. please help. thanks

Hi Jennette,

The report can drill just as deep as the Parent – Child (or any recursive hierarchy for that matter) can go. But you can only define one parent and one child. If what you have isn’t a recursive hierarchy but rather a basic hierarchy with 5 levels, use the usual report grouping.

All the best,

Ella

Hi,

I’m new in the SSRS, and i’m having a problem creating a measure to do a apparently easy SUM with a condition!

Can someone help me?

What i have is:

[Dm Plano Contas].[Snc Cod].[1]

…

[Dm Plano Contas].[Snc Cod].[23]

[Dm Plano Contas].[Snc Cod].[231]

[Dm Plano Contas].[Snc Cod].[2311]

[Dm Plano Contas].[Snc Cod].[2312]

…

And what i want is to SUM all the childrens of the [Dm Plano Contas].[Snc Cod].[23] where the [Measures].[Saldo] is smaller than zero.

Hi Nuno,

From the example it looks like what you want is to check that measure for all the descendants of that member as the key is either 3 or 4 letters long (which I guess means different levels). Using MDX functions you can write something along the lines of:

Sum(YTD(), iif(([Measures].[Saldo], [Dm Plano Contas].[Snc Cod].currentmember) <0, [Measures].[Saldo], 0)

Create that sum as a new measure in your cube and use that as a measure for the recursion in SSRS I described in the post.

I would show in the report with just the relevant members as I don’t see the logic in this measure for any member that isn’t under [Dm Plano Contas].[Snc Cod].[32].

Please also look into the children, descendants and members MDX functions if they’re at all relevant for you.

Hope that helped,

Ella

how to do the average of average in a matrix .