Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

SSRS Matrix Report

by suresh

In this article, we will show you how to create a Matrix Report in SSRS (SQL Server Reporting Services) with a practical example.

The below screenshot will show you the Data Source and Dataset we used to create this SSRS Matrix Report.

MATRIX REPORT IN SSRS 2014

SQL Command we used for this SSRS Matrix Report in the above screenshot is:

SELECT Geo.[EnglishCountryRegionName] AS [Country]
      ,Geo.[StateProvinceName] AS [State]
      ,Geo.[City]
      ,Cust.FirstName +' '+ Cust.LastName AS [Full Name]  
      ,Cust.EnglishEducation AS Education 
      ,Cust.EnglishOccupation AS Occupation
      ,Cust.Gender
      ,SUM(Cust.YearlyIncome) AS YearlyIncome
      ,SUM(Fact.SalesAmount)AS SalesAmount 
FROM  DimCustomer AS Cust
  INNER JOIN
      FactInternetSales AS Fact ON 
 Cust.CustomerKey = Fact.CustomerKey
 INNER JOIN
     [DimGeography] AS Geo ON
   Cust.GeographyKey = Geo.GeographyKey
 GROUP BY Geo.[EnglishCountryRegionName]
         ,Geo.[StateProvinceName] 
	 ,Geo.[City]
	 ,Cust.FirstName
         ,Cust.LastName  
         ,Cust.EnglishEducation 
         ,Cust.EnglishOccupation 
         ,Cust.Gender
  ORDER BY [Country]

Creating Matrix Report without using Report Wizard in SSRS

Please refer to the SSRS Table Report to understand the creation of a basic Report. Drag and drop Matrix from the SSRS Toolbox to the Data region.

MATRIX REPORT IN SSRS 2014

We can add the Column names to the SSRS Matrix Grouping in three ways:

  • We can Drag the Column name from Report data to the Row Grouping or Column Grouping Column.
  • Drag and Drop the Column name from Report data to the Row Grouping Pane or Column Grouping Pane.
  • When we click on the particular cell, it will open the context menu displaying available column names in that Dataset. We can select the required column from the list. Here we are selecting StateColumn as a Row Grouping item
MATRIX REPORT IN SSRS 2014

Now, we are choosing Occupation Column as a Column Grouping item

MATRIX REPORT IN SSRS 2014

and we are selecting the Sales Amount Column as a Data item.

MATRIX REPORT IN SSRS 2014

NOTE: When you add Column to Data Column, the Reporting Server will automatically aggregate the data. If you want to change the aggregate functions, then goto fx Expression and change them accordingly.

We successfully created our first SSRS Matrix report. If you observe the below screenshot, we have done the formatting as well. Please refer to Format Fonts and Background Color of a Textbox article to understand them.

MATRIX REPORT IN SSRS 2014

Click on the Preview button to see the report preview

MATRIX REPORT IN SSRS 2014

Add Header Column For Column Grouping in SSRS Matrix Report

If you observe the above screenshot, we all know that Clerical, Management, Manual, etc., are the Customer Occupation. What if we don’t know, or we want to provide extra information to the user about each column? In these situations, we have to add row (containing Header Details of the columns).

To add a new row to SSRS Matrix Report, Please select the Header column of a report and then right-click on it to open the context menu. From the menu, Please select the Insert Row option. Here we have multiple options, and their functionalities are:

  • Inside Group – Above: This option inserts a new row on top of the Header column, and inside the Group. Inside the Group means Header text will repeat for every row
  • Inside Group – Below: This option will insert a new row below the Header column and inside the Group.
  • Outside Group – Above: It inserts a new row on top of the Header column and Outside the Group. Outside the Group means Header text will be static and will not repeat for every row
MATRIX REPORT IN SSRS 2014

We named the Header text as Profession as shown below

MATRIX REPORT IN SSRS 2014

If you observe the below screenshot, we have done the background formatting.

MATRIX REPORT IN SSRS 2014

Click on the Preview button to see the SSRS Matrix Report preview

MATRIX REPORT IN SSRS 2014

Placed Under: SSRS

  • Learn SSRS in 28 Days
  • Create New SSRS Project
  • Create a New SSRS Report
  • SSRS Query Designer
  • SSRS Shared Data Source
  • SSRS Shared Dataset
  • SSRS Embedded Dataset
  • SSRS Embedded Data Source
  • SSRS Lookup – combine datasets
  • SSRS Report Items
  • SSRS Table Report
  • SSRS Table using report wizard
  • Format SSRS Table Report
  • SSRS Matrix Report
  • SSRS Matrix with report wizard
  • Display Image in SSRS Report
  • Add Rectangle to SSRS Report
  • Add TextBox to SSRS Report
  • Create a SSRS List Report
  • Grouping in SSRS Matrix Reports
  • Grouping in SSRS Table Reports
  • Create SSRS Report using Views
  • SSRS report with store procedure
  • Add Total and Subtotal to Report
  • Add Headers & Footers to Report
  • SSRS Global References
  • Export Report to PDF, XML, Excel, Word
  • Format SSRS Table Report
  • Format Textbox Fonts & Bg Color
  • Format Numbers in SSRS Report
  • Format SSRS Report Date, Time
  • Add Row Numbers to Report
  • Headers Visible While Scrolling
  • Repeat Headers on Each Page
  • SSRS Sorting
  • SSRS Interactive Sort
  • Filters at Dataset Level in SSRS
  • Filters at Tablix Level in SSRS
  • Page Breaks in SSRS Report
  • Custom Code in SSRS Report
  • SSRS Calculated Fields
  • SSRS Dashboard Reports
  • SSRS Document Map
  • SSRS Drill Through Reports
  • SSRS Drill Down Reports
  • SSRS Drill Down Matrix Report
  • SSRS Go To URL Action
  • SSRS Subreports
  • SSRS Report Parameters
  • SSRS Cascading Parameters
  • SSRS Multiple Parameters
  • SSRS Multi Value Parameter
  • SSRS Drop Down List Parameters
  • SSRS Charts
  • SSRS Area Chart
  • SSRS Bubble Chart
  • SSRS Column Chart
  • Format SSRS Column Chart
  • SSRS Data Bars
  • Data Bars in SSRS Matrix Reports
  • Format SSRS Data Bars
  • SSRS Funnel Chart
  • SSRS Linear Gauges
  • SSRS Radial Gauges
  • Format SSRS Gauges
  • SSRS Indicators
  • Format SSRS Indicators
  • SSRS Line Chart
  • SSRS Pie Chart
  • Format SSRS Pie Chart
  • SSRS Pyramid Chart
  • SSRS Range Chart
  • SSRS Radar Chart
  • SSRS Scatter Plot
  • SSRS Sparkline
  • SSRS Stacked Bar Chart
  • SSRS Configuration Manager
  • Change Report Manager Title
  • SSRS Deploying Reports in BIDS
  • Deploy using Report Manager
  • SSRS Report Manager Shared DataSource
  • SSRS Cached Reports
  • SSRS Linked Reports
  • SSRS Report Parts
  • SSRS Report Snapshot
  • SSRS Report History Snapshot
  • SSRS Report Subscription
  • SSRS Security
  • Upload File to Report Manager
  • Install SSRS Report builder
  • Report Builder – Create a Report
  • Report Builder – Create a Dataset
  • Report Builder- Create Bar chart
  • Report Builder – Create Pie Chart
  • Report Builder – Create report without wizard
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy