Matrix Reports in SSRS

Matrix Reports

A Matrix is very similar to a table, but it is configured to show data grouped by columns and rows, with aggregate data at the intersections. Some of the data you might expect to see in a row can actually become a column header. This is similar to using a pivot table in a spreadsheet.

If you would like to Enrich your career with a SSRS certified professional, then visit Mindmajix - A Global online training platform: “SSRS Online Certification Training”  Course. This course will help you to achieve excellence in this domain.

Aggregate data

Aggregate data is data that has had a aggregation function applied to it, such as SUM, which simply adds up all the values in that group.

Data can be aggregated in the database query or in the report designer. In the queries you have been using so far, the TotalDue value has been aggregated by the SUM function in the query before it even gets returned by the database.

In the Try it for this section, the data is grouped to show years as the rows, names as columns and the total (Sum) of sales for each year/name combination in the data cells.

Try it

1. Create a new report named Matrix by right-clicking on Reports in the Solution Explorer choosing Add > New item.

2. Create a Report data source called AdventureWorks that references the shared data source of the same name.

3. Create an embedded dataset named Matrix that uses the report data source and use the following query:

          p.FirstName + p.LastName AS FullName,
          SUM(soh.TotalDue) AS TotalDue,
          YEAR(soh.OrderDate) AS [Year],
          MONTH(soh.OrderDate) AS [Month]
          Sales.SalesOrderHeader soh
          INNER JOIN Person.Person p
          ON p.BusinessEntityID = soh.SalesPersonID
          p.FirstName + p.LastName,

Frequently Asked SSRS Interview Questions & Answers

4. Click OK to return to the report design surface.

5. Drag a Matrix from the Toolbox on to the report.

MindMajix Youtube Channel

6. Switch to the Report Data panel and drag fields from the Dataset onto the matrix, so that FullName is the Column, Year is the Row, and TotalDue is the Data. Note that TotalDue is automatically converted to a SUM function.

7. Preview the report.

Report Data panel

-----     Related Page: Adding Charts To A SSRS Report (Report Builder)     -----

Grouping in a Matrix report

Grouping in a matrix is just like grouping in a table. In fact, a table and a matrix are so similar that you will often see them referred to as a ‘Tablix’. It is also possible to convert a table into a matrix by adding grouping, so working with any of them (and lists as well, which are another variation) should feel familiar if you’ve used at least one type.

Try it

1. Using the Matrix report you built in the previous section, in Design view, right-click on the cell that contains “SUM(TotalDue)” and choose Add Group > Row Group > Child Group from the menu

2. Select Month from the drop down list and select Add Group Header.

SSRS Tutorial

3. Click OK and preview the report.

Note that (unless you’re ahead of the game and changed it already) the month is displayed as a number. Edit the expression for this cell so that it uses the MonthName function to display the name of the month instead.

To make the report a little easier to read:

1. Right-click on the Month data cell («Expr») and choose Insert Row > Outside Group Above.

2. Select the inserted row using the row selector on the left, and set a background colour other than white.

Month data cell

3. Preview the report.

Explore SSRS Sample Resumes! Download & Edit, Get Noticed by Top Employers!                Download Now!


List of Related Microsoft Certification Courses:

 SSIS Power BI
 SSAS SQL Server
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator


Course Schedule
SSRS TrainingJul 20 to Aug 04View Details
SSRS TrainingJul 23 to Aug 07View Details
SSRS TrainingJul 27 to Aug 11View Details
SSRS TrainingJul 30 to Aug 14View Details
Last updated: 03 Apr 2023
About Author

Ravindra Savaram is a Technical Lead at His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read less