MATRIX REPORT IN SSRS

In this article, we learn how to create a matrix report in SSRS (SQL Server Reporting Server).

MATRIX

• The matrix table has a combination of grouping on columns and rows.
• In Solution Explorer right-click on Reports and add New Items.

SOLUTION EXPLORER

• Here we select Report and enter a report name and click on ok button.

ADD NEW ITEM

• 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.

SHARED DATA SOURCE PROPERTY

• Now we provide the Server name and database name. Then click on ok button.

CONNECTION PROPERTY

• Here connection string will be displayed.

SHARED DATA SOURCE PROPERTY

DATA SOURCE

• 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.

ADD DATA SOURCE

• In Data Source Property we change the name and we use shared data source reference.

DATA SOURCE PROPERTY

DATASET

• 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.

DATASET PROPERTIES

• 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.

QUERY DESIGNER

• 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.

TOOLBOX

• In the text box, we enter the heading of the report.

TEXTBOX

• Right-click on the textbox and configuring the Font.

CONFIGURE TEXTBOX

• 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.

BEFORE MATRIX

• Also, we can drag-drop the column name from the Report Data

AFTER MATRIX

Start with MSBI video as mentioned below to learn topic practically in-depth: