Recursive Sum in a Matrix in SSRS 2008
I read a few months back a post by Robert Bruckner saying that the InScope function isn't relevant anymore, when you’re creating a report with Tablix.
I remembered that when I wrote about a recursive sum for a group in a matrix in SSRS my example was only about SSRS 2005, as my VPC with SSRS 2008 crashed… well I finally downloaded SSRS 2008 R2 and installed it. So what did I do (after checking out the new maps feature)? Well, I wanted to see how simple creating a recursive sum in a matrix had actually become. I created a report in Report Builder 3.0, using again the Adventure Works 2005 Sample DB. I used the Employee Full Name against the Product's Product Line and the Sales Amount. I set up the recursive part by defining the row group on the FullName row group which groups on the EmployeeKey:
and the parent group (ParentEmployeeKey) to that on the advanced tab:
And that was actually it!I didn't bother creating a subtotal anymore. Just the totals for each attribute (be it the Product Line or the Full Name of the Employee). Didn't need to define InScope as an expression for the sum. Nothing! Creating a recursive sum in a matrix in SSRS 2008 is as simple as creating it in a table. I have to admit I was amazed.
So first of all I have to take my hat off to everybody at the SSRS team who are making the creation of reports more and more like child's play. You're getting the same report you got with SSRS 2005, with less trouble. That also means that your subtotals are again just for the employee inside the matrix, (and not for the employee and the workers who work under him in his team – see Abbas Syed again as an example).
That problem has not been solved, but creating the report in general has become simpler. If you still need it, InScope still exists in the function list:
So if you're trying to create a recursive sum in Reporting Services 2008, you may have discovered this – InScope is dead, long live SSRS 2008!