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

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

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