How to Create a SSRS Matrix Report ?
A SSRS Matrix report is very similar to a Tabular report, but it displays a data grouped by columns and rows, with aggregate data at the intersections. It is similar like a SQL Pivot to transform data from row-level to columnar data .
To Creating a SSRS Matrix Report, you need to create new project solution then add a data source, and data set to fetch a data for report else you can also take an existing project solution.
If you are new to SSRS report then, you can read the following posts.
How to create a new project in SSRS ?
How to create a Shared Data Source and Shared Data Set in SSRS ?
After Creating a project solution, Right click on Reports Folder.
Then, Click on Add > Next , Select New Item…
After selecting New Item… , a Add New Item window opens.
> Select Report > Specify Report Name > Click on Add button
After clicking on Add button, you can see a Design window tab, that is your blank report page.
Now time to configure a data source and dataset for this report. If you have already a shared datasource, or shared dataset then you can use them. Otherwise you have to specify a new dataset and data source for report.
Here we have a shared dataset, and datsource.
Right Click on DataSources Folder > A Data Source Properties window opens
In General Tab > Specify DataSource Name > Now we have two option to choose
- Embedded Connection
If you want to create a new datasource for this report then you have to choose Embedded connection radio button and select Microsoft SQL Server as type then click on Build… to create a connection string and DataSource.
- Use Shared Data Source Reference
If you want to use an existing datasource then you can choose this option.
Here we are using an existing database that’s why selected a shared datasource option.
Now create a Dataset, you can also use an existing shared dataset if you already have.
Before creating a dataset, Lets prepare a table and data for reports in SQL Server.
Create table SalesData (SalesId Int identity(1,1) , Product varchar(50), SalesDate Date , Qty Int )
Now Insert some records into SalesData table.
Insert into SalesData values ('Hardware','2019/01/03' ,50) ,('Hardware','2019/01/18' ,100) ,('Hardware','2019/02/05' ,100) ,('Hardware','2019/02/15' ,60) ,('Hardware','2019/03/19' ,590) ,('Hardware','2019/04/19' ,90) ,('Hardware','2019/05/19' ,45) ,('Software','2019/01/05' ,570) ,('Software','2019/02/11' ,200) ,('Software','2019/02/09' ,180) ,('Software','2019/03/14' ,610) ,('Software','2019/04/13' ,50) ,('Software','2019/05/11' ,20) ,('Software','2019/05/30' ,785)
Now create a query which returns a sales data month wise.
Select Product ,DateName(Month,SalesDate) as Mnth, Qty from SalesData
Now create a dataset, Right click on Dataset Folder > a Dataset Properties window opens> then, In a Query tab Specify a Name for Dataset.
Now choose either Use a shared Dataset – To use an existing dataset or use a dataset embedded in my report – to Specify a new dataset.
Select DataSource from a dropdown list ( if you already have an existing datasource ) else create a new datasource.
Next, Provide a query that will be used to show data in report. here we provide a query that we have prepared.
Next, finally click on OK button to finish dataset step.
Now you can see , Datasource , and dataset are created .
As you can see in above screenshot, column lists is not showing in Dataset1, sometimes it does not show so you have to specify them manually and if it is showing then no need to create them manually.
So to add columns in datasource > right click on Dataset1 > a Dataset Properties window opens.
In Fields Tab > we can see a blank row having two columns : fieldName and FieldSource.
For FieldName: you can give column Name and in FieldSource: you have to specify a corresponding query field value of that column, it must be same as defined in Query.
Add or delete button– Add button is used to add a blank row and delete is used to delete an existing row.
when you add a row, it asks to select either calculated field or Query Field, so first, delete that blank row first as it is auto selected by default for a calculated field .
Calculated Field – if you want to give some expression or custom formula for this column.
Query Field – if you want to use a query column.
So here we want to display only column data that are returned by dataset, so select a query fields only.
finally we click on OK button at the bottom in a dialog box.
We can also see the report bottom , which columns is going to be grouped based on rows and which going to be grouped based on columns.
Now you have to create a report that will display a sales data – Total sales of product month wise .
Whatever you want to show in a row, simply drag those columns into rows and drag those fields into column that want to display in a column’s headers of report, and drag those field into Data, that you want in grouping.
Now click on preview tab to see the report output – it is a simple Matrix report.
Now, will add a Grand total in a report, go back to design window.
Go to Row Groups at the bottom of report > then right click on Product > then, Add total > Next, Click on After in a context menu.
Once you clicking on After, It adds a total row in Report
To Change the column header colors.
> Select columns header > click on back ground Color Icon > then , pick Color from tiny dialog box > Click on OK button.
You can also customize fonts, and text alignment.
Report can also be customized through properties dialog box.
Click on any column, or headers, whatever Item you select on report and then press ctrl + F4 key, it opens a properties for that selected item on report.
After finishing up all customization, you can click on Preview tab to see the final report.
