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 -
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.
ReplyDeletePhp course in chennai
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.
DeleteIEEE 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
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
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThere's no doubt i would fully rate it after i read what is the idea about this article. You did a nice job.. 托福代考
ReplyDeleteI 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