In this article, we learn how to create a matrix report in SSRS (SQL Server Reporting Server).
- The matrix table has a combination of grouping on columns and rows.
- In Solution Explorer right-click on Reports and add New Items.
- Here we select Report and enter a report name and click on ok button.
- SSRS supports two types of data sources: Shared Data Source and Embedded Data Sources.
SHARED DATA SOURCE
- If we want to share the same data source for multiple reports, report models, and subscriptions, then we have to create a shared data source.
- It can be created once and used in multiple reports.
- Shared data source is a combination of Provider name, Server instance Name, Database Name, and Database credentials.
- Right-click on Shared Data Source properties go to General tab and change name, type, and connection options to click on Build.
- Now we provide the Server name and database name. Then click on ok button.
- Here connection string will be displayed.
- Data Source is a connection setting that is used to connect with underlying Databases.
- Go to the View tab in that select Report Data. Here we add a Data Source.
- In Data Source Property we change the name and we use shared data source reference.
- Datasets is an SQL command used to retrieve data form database. There are two types of datasets: Shared Dataset and Embedded Dataset.
- In this wizard, we choose a data source and create a query by using a query designer.
- Using Query Builder we design a query.
- In the query designer, we add a table by right-clicking on the white space area. select the column and click on ok.
- Click to finish and let us see in the datasets folder in report data.
- In Design drag and drop the text box as well as Matrix from the toolbox. Toolbox is available in the view tab.
- In the text box, we enter the heading of the report.
- Right-click on the textbox and configuring the Font.
- In the matrix, we click on the particular cell then it will open available column names then we select the required column name from the list.
- Also, we can drag-drop the column name from the Report Data
Start with MSBI video as mentioned below to learn topic practically in-depth: