Ella Maschiach's BI Blog

April 22, 2009

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.

Now, the thing was that if I used the simple recursive sum I used for my table, I would get the recursive sum for the parent employee, but it would be the same for all the product lines. Obviously, this was not what I was looking for.At this point, I would really like to thank Boris Kogan who introduced me to the 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:

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

Which gives us:

But you might still ask “What about a recursive sum in the cells?” Well, I do admit my solution isn’t full…

I did try to apply:sum(Fields!SalesAmount.Value,“matrix1_FullName”,recursive)

For the sum in the cell but I have to admit it didn’t work. What I got was the total for each employee for all of his sales (including his children, if he’s a parent). That’s all his sales – not just for that product line. I even tried adding another iif clause where I defined that only level zero (of the parent) will get the recursive sum and the rest will get the usual sum. That also didn’t help.

Thing is, that the recursive sum actually needs to run on two levels of scope for it to appear correctly. We need the recursive sum for both groups we have in the matrix – both the employee and the product line, because we’re actually trying to sum the sales of an employee and all his workers for a specific product line (not just their sales in general). In the recursive sum though, you can only give one scope for it to run on – either the employee or the product line. You cannot define it on two scopes. So in the solution I’m proposing, you only see the recursive sum in the column group subtotal.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.

In the meanwhile, I’m attaching my report for your viewing pleasure: Sum Recursive MatrixI hope this helped you create the recursive sum you need for a group in your matrix.

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.

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.

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.

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.

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

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.

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: