Friday, April 29, 2011

How to Create Subtotals in a Matrix in SSRS (2005/2008)

How to Create Subtotals in a Matrix in SSRS (2005/2008)

How to create and use subtotals in your Matrix objects.

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 :

1. Open or create a new SSRS project

2. Add a report item to your project.

3. Add a dataset

4. Add a matrix object

5. Configure matrix to desired specification

6. Add totals to outer groups by right-clicking the outer column/row group for which you require the subtotals and selectsubtotal.

7. Add subtotals for inner groups by right-clicking the inner column/row group for which you require the subtotals and selectsubtotal.

8. Tidy up by hiding the inner group line items initially – by right-clicking the inner group and selecting Properties>Visibilityand 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: