Matrix Reports in SSRS (Reporting Services)
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.
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.
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]
INNER JOIN Person.Person p
ON p.BusinessEntityID = soh.SalesPersonID
p.FirstName + p.LastName,
4. Click OK to return to the report design surface.
5. Drag a Matrix from the Toolbox on to the report.
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.
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.
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.
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.
3. Preview the report.