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:

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

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.

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.

-----     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.

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
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator