SSRS – How to create a matrix report in SSRS

Matrix is a wonderful feature in SSRS to display grouped data on the summary level to organise our business data in a better manner. Matrices provide functionality similar to cross-tabs and pivot tables as we did in Excel. On execution time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. After our initial design, we can continue to develop a matrix to improve the viewing experience for the user.

To understand the functionality of Matrix in SSRS, we can take an example of daily sales in SalesDetails table where users want to see the item performance in the various sales regions as given below:

Region	OrdNo	OrdDate	Item	SalesCash
10 - North India	20160106	12/15/2016	2052 - Inkjet Red Ink	8006.7
10 - North India	20160106	12/15/2016	2053 - Inkjet Blue Ink	6518.2
11 - South India	20160102	12/11/2016	2052 - Inkjet Red Ink	3284.8
11 - South India	20160107	12/16/2016	2051 - Inkjet Printer	2677.5
11 - South India	20160107	12/16/2016	2053 - Inkjet Blue Ink	6518.2
12 - East India	20170104	4/1/2017	2052 - Inkjet Red Ink	8006.7
12 - East India	20170104	4/1/2017	2053 - Inkjet Blue Ink	6518.2
13 - West India	20170108	6/1/2017	2051 - Inkjet Printer	2677.5
13 - West India	20170108	6/1/2017	2053 - Inkjet Blue Ink	2883.05
14 - Middle India	20160110	12/19/2016	2052 - Inkjet Red Ink	25149.25
14 - Middle India	20160110	12/19/2016	2053 - Inkjet Blue Ink	2883.05

In SQL Server Data Tools 2015 or Report Builder, we have the blank report with the SQL Server datasource as given below: 

In Report Data, right clicks on Datasets and click on Add Dataset as given below:

After clicking on Add Dataset, it will launch Dataset properties window and we need to set name of Dataset as dsSales, choose to use a dataset embedded and choose query type as Text here as given below:

 

After clicking OK button on the Dataset Properties window, a new dataset has been added in Report Data under Datasets section as given below:

Now, we have report dataset in our report and need to add the matrix over there.  To add a Matrix on the report, just right click on the report body and choose matrix from the Insert section as given below:

After clicking on the Matrix, a matrix has been added in our report as given below:

The matrix initially has a row group, a column group, a corner cell, and a data cell, as shown in the above figure where Values in matrix cells (data cell) display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. 

Now just select your matrix on the report and go to the properties of matrix and set the DataSetName as dsSales over there as given below:

After adding the dataset to the matrix, add groups by dragging dataset fields to the Row Groups and Column Groups areas of the Grouping pane. The first field that we drag to the row groups or column group’s pane replaces the initial empty default group as given below: 

After dragging the requested columns from the datasets to the report, we can apply formatting for each cell, depending on the data as given below:

In Preview, the matrix expands to show the row group and column group values. The cells display summary values, as shown below:

In Preview, we can see that values in matrix cells (data cell) display aggregate values scoped to the intersection of the row and column groups to which the cell belongs to give a clear understanding of the sales data.

Conclusion

In SSRS, a Matrix control provides a crosstab view and pivot tables of data, similar in behavior to a PivotTable in MS Excel. We can apply formatting for each cell and have the facility to write the conditional expression and custom code.  We can also include drilldown toggles that initially hide detail data; the user can then click the toggles to display more or less detail as needed. For the dynamic columns, matrix is the best option in SSRS.

For the live demo, you can visit us at youtube also and subscribe our channels for more tutorial videos -