This is a quick look at how to create and use subtotals in your Matrix objects.
Subtotals 101
SQL Server Reporting Services makes it relatively easy to add totals to your columns and rows in a matrix object. In short, it’s as easy as right-click>Subtotal. Seriously, that’s it. Well, unless you want to format them, but that’s another issue entirely (an not quite as straight forward).
Let’s do this step-by-step :
- Open or create a new SSRS project
- Add a report item to your project.
- Add a dataset
- Add a matrix object
- Configure matrix to desired specification
- Add totals to outer groups by right-clicking the outer column/row group for which you require the subtotals and select subtotal.
- Add subtotals for inner groups by right-clicking the inner column/row group for which you require the subtotals and select subtotal.
- Tidy up by hiding the inner group line items initially – by right-clicking the inner group and selecting Properties>Visibility and then setting the properties accordingly.
Here’s a quick 2 minute screen cast of how to create subtotals in a Matrix object in SSRS 2005. It illustrates creating row group Totals & subtotals, but applies to column groups as well:
Here’s a link to another version if you are having any trouble: http://www.screencast.com/t/OGE1MmI4
Very Use Full
Nicely done!
You made it look so easy. Now I need to figure out how to add multiple subtotals based on the grouping in the matrix
To Add multiple subtotals: repeat steps 6/7 for as many totals/subtotals as you need and then define each one according to your requirements.
For SSRS 2008, right-click the cell (in the matrix) that you wish to add an total to, click “Add Total” and select either “Before” or “After”, depending on your preference. Alternatively, you can access the “Add Total” menu in the Groups section at the bottom of the BIDS Designer.
Hi,
I want to customise the subtotal fields. below is described my matrix structure:
I have added Column group on Score field value in matrix and Row Group on City field value. In data cell I am showing the count of items for each city falling in a particular score.Ex:
Score 1 2 3 4
City ————————–
Mumbai 2 0 0 0
Delhi 0 1 1 0
Chennai 3 1 0 2
Now for this matrix I want to show row total at the end for each score but this total is based on some other columns value instead of the itemCount value that is shown here.
Can you please help me out for this? how can i add/show subtotals based on the columns that are not shown or included on matrix groups.
please suggest
thanks
Pingback: Blog Stats – 2010 in a nutshell | Luke Hayler - SQL Server Developer
Very useful. Thanks for the post.
i was never use matrix or tablix in my report becouse i dont know tool very well but i will see that post and Switch over to the tablix
thank u
I can’t thank you enough!
Very useful. Thanks for the post.