Monday, February 6, 2017

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 -

No comments:

Post a Comment

Popular Posts