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 -

9 comments:

  1. This is an awesome post. Really very informative and creative contents. This concept is a good way to enhance the knowledge. I like it and help me to development very well. Thank you for this brief explanation and very nice information. Well, got a good knowledge.
    Php course in chennai

    ReplyDelete
    Replies
    1. IEEE Final Year Project centers make amazing deep learning final year projects ideas for final year students Final Year Projects for CSE to training and develop their deep learning experience and talents.

      IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation.

      corporate training in chennai corporate training in chennai

      corporate training companies in india corporate training companies in india

      corporate training companies in chennai corporate training companies in chennai

      I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai Project Centers in Chennai

      Delete
  2. A good optimization campaign optimizes the site architecture, text content, and code of the site. Assembly line SEO does not take into consideration the unique needs/design of the site and may even deal in blackhat optimization. best seo company for small business

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. There's no doubt i would fully rate it after i read what is the idea about this article. You did a nice job.. 托福代考

    ReplyDelete
  5. I found your this post while searching for some related information on blog search...Its a good post..keep posting and update the information. commercial electrical services

    ReplyDelete
  6. Nice post. I was checking constantly this blog and I’m impressed! Extremely useful info specially the last part I care for such information a lot. I was seeking this certain info for a long time. Thank you and good luck. csgo high tier accounts

    ReplyDelete
  7. This is actually the kind of information I have been trying to find. Thank you for writing this information. Valorant Smurf Accounts

    ReplyDelete
  8. I am unable to read articles online very often, but I’m glad I did today. This is very well written and your points are well-expressed. Please, don’t ever stop writing. pubg mobile accounts

    ReplyDelete

Popular Posts