Creating Matrix Report in SSRS 2014

In this article we will show you, How to create Matrix Report in SSRS (SQL Server Reporting Services) 2014. Below screenshot will show you the, Data Source and Dataset we used to create this SSRS Matrix Report.

SQL Command we used in the above screenshot is:

T-SQL QUERY

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 SSRS Table Report to understand the creation of basic Report. Drag and drop Matrix from SSRS toolbox to the Data region.

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

We can Drag the Column name from Report data to the Row Grouping or Column Grouping Column.
We can drag and Drop the Column name from Report data to the Row Grouping Pane or Column Grouping Pane.
When we click on particular cell, it will open the context menu displaying available column names in that Dataset. We can select the required column from the list as shown in below screenshot. Here we are selecting State Column as a Row Grouping item

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

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

NOTE: When you add Column to Data Column, 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 Format Fonts and Background Color of a Textbox article to understand them.

Click on the Preview button to see the report preview

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 what if we want to provide extra information to the user about each column? In these situations we have to add extra row (providing Header Details of the columns).

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 option and their functionalities are:

Inside Group – Above: This option will insert a new row on top of the Header column and inside the Group. Inside the Group means Header text will be repeated for each and every row
Inside Group – Below: This option will insert a new row below the Header column and inside the Group.
Outside Group – Above: This option will insert 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 be repeated for each and every row