April 2009 - Posts
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: =Iif(InScope("matrix1_ProductLine"),
Iif(InScope("matrix1_FullName"),
sum(Fields!SalesAmount.Value),
sum(Fields!SalesAmount.Value,"matrix1_ProductLine")),
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).
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 Matrix
I hope this helped you create the recursive sum you need for a group in your matrix.
Well I guess this means Panorama wants a bigger piece of the pie. In a very impressive and interesting webcast which was given Tuesday, March 31st, Panorama showcased Nova View 6 RC1. So what are the promises given in the webcast and what was shown?
- Well for me, the biggest news was that from now on, Panorama Nova View will connect not just to cubes, but also to RDBMS – yes, a relational data source! And, you'll be able to create a dashboard which includes a report on both data sources. This however, was not demonstrated in the webcast so I am waiting to see it in the final release.
- Panorama Shared Views - a chance to collaborate outside the firewall. Further enhancing their partnership with Google, Panorama will now offer its users to share reports done with Nova View, through iGoogle to other authorized users who also have a Google account.
- Flash Analytics – Panorama has developed a new product based on Flash to develop new and better looking reports. Most of the demo was dedicated to this product. I have to say I really like what they did with the UI there, it was much better looking. First of all, they put the "Help" as a small note in the bottom of the report, which will show the relevant explanation by the part of the view that you clicked on. I think that really helps you see and understand the whole story at the same time, instead of the usual switching back and forth from the explanation to the actual part.
You can also expand and collapse an attribute in the same pane, rather than having it open in a new window. It's like everything is finally put in one place. Flash Analytics also has a more accessible way for you to format your formulas, manipulate your threshold and more. Obviously, the new product also offers very nice flash charts. Flash Analytics can also open reports done in previous versions of Nova View.
Flash Analytics does not yet offer the full capabilities of the Nova View Desktop client, but should offer it all by the time of the SP1 release of this version. There is a comparison between the Desktop (Java) client tool and the Flash Analytics available. The Desktop Analytics client has not been changed.
- Another product which was showcased was Executive Dashboards which again uses flash to create templates for dashboards.
- I also really liked seeing Panorama Spotlight, which enables you to embed KPIs from your cube and quick links to your views and that all inside your Outlook.
To have a look at the webcast showcasing Panorama Nova View 6 RC1, please write to Panorama Support, so you can either view an old recording or participate in future ones. In the meanwhile, you can read further about the new products that Panorama is scheduling for release with this version and also read in general about Panorama Nova View 6. And for a limited time only, you can also register to download Nova View 6 Release Candidate 1. The final version of Nova View 6 is scheduled for release mid June. I think things just got more interesting :)…
Microsoft continues in helping you learn SQL Server 2008, this time in collaboration with AppDev. You can download up to three hours of training. If you haven't used AppDev courses till now then you should know that they really walk you through the subject they're covering, also giving you an exam at the end of each part. It really makes for a great way to learn on whatever it is you're interested in. The courses offered are: SQL Server 2008 for Developers, SQL Server 2008: Installing, Querying, and Security and Exploring SQL Server 2008. You can also choose to learn about Visual Studio 2008 instead.
So if you're interested then go and do the training for SQL Server 2008 – it's free!