Recursive Sum for a Group in a Matrix in SSRSI 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).
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:
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 Matrix
I hope this helped you create the recursive sum you need for a group in your matrix.