SSRS – Matrix Report

Often when you create a SSRS report you choose to display your data in a Table, which means the rows are repeated straight down each column. For certain reports, a way to improve readability is to use a Matrix report. This pivots one of the rows into a column moves the rows into the appropriate column, sort of like a Pivot Table in Excel. Sound confusing? It is not, just look at the example below.

Below is a query that summarizes amounts by Class ordered by date. There is one row per month for each Class.

We can use the same query for our report so we created a new dataset and pop the query in there.

Now go to the Toolbox and either double click or drag over the Matrix component. If you hover over the component, it gives you a brief description on what it does. I recommend you do this for everything in the toolbox as there is some very useful built-in functionality.

This will put in the Matrix component on your report. You can see it is asking what you want for the: rows, data and columns.

I click in the rows box, and select PostMonth for the rows section.

For the DATA section, I choose the TOTAL field.

Finally, for the COLUMNS, I select Distribution Class. This will create a new column for each Class automatically.

This is what it looks like after selecting the fields. I also changed the column heading names and made them bold.

Let’s preview the report and see how it looks. Looks pretty good.

Now you have one row per month, and the Distribution Classes have been moved to separate columns so it is easier to get totals.

Speaking of totals, let us add a total to each column.

Right click the Total cell, and select Add Total -> Rows.

This will create a Total group in your report. I bolded this row to show it off better.

Now let’s preview it again.

Now we have a total row!!!

What happens if we add a new Distribution Class? Will we need to change our dataset or our query?

No! The Matrix component will adjust to the data.

For our example, I am adding a new distribution class called S/O-MTS. This is fake data and just going to be used for our example.

Now we run the report again and the new column is automatically added to the report with no changes required by the report writer!

To read more about the Matrix component, follow the link below. https://docs.microsoft.com/en-us/sql/reporting-services/report-design/tables-matrices-and-lists-report-builder-and-ssrs?view=sql-server-ver15