Show All Months In SSRS Matrix Report

Show All Months In SSRS Matrix Report

Added by Sixth Sense updated on Wednesday, June 26, 2019

Problem:

I need to create a matrix report using SQL Server Reporting Services (SSRS) that can show all months in the column header even for the months that have missing data.

Below is the yearly report and we see some months are missing data and therefore the column headers don't show.

sale report matrix

The expected output is shown below:

sale report matrix

This tip explains the steps to develop the matrix report to show column headers for all months even when there is no data for these columns.

Solution:

For this solution, I am using the below T-SQL query to develop the SSRS report. As you can see, the below query is returning Sales Amount for all the employees for a given year. If we pass the year value we are going to get the data for the months within that year.

SELECT 
   b.FirstName+' '+b.LastName as EmployeeName
   ,[SalesAmountQuota]
   ,MONTH([Date]) as CalenderMonth
   ,YEAR([Date]) as CalenderYear
FROM [dbo].[FactSalesQuota] as a
INNER JOIN [dbo].[DimEmployee] as b on a.EmployeeKey=b.EmployeeKey
WHERE YEAR([Date])=@Year
				

However, in this scenario there are a few months that have no data and therefore those months do not show in the matrix report headers. The idea is to modify the T-SQL query to include the missing months, so they display in the matrix headers.

SSRS Report Development:

For this solution, I am using a T-SQL query from the AdventureworksDW2016 database and SQL Server Data Tools 2015 version in order to develop this SSRS report.

The first step is to modify the above query in order to include the missing months into the dataset. I will first declare a table variable called @months, then I will write the below T-SQL and using a loop to load the @months table will all the month numbered from 1 to 12.

DECLARE @months table (MonthNum int)
DECLARE @i int =1

WHILE (@i<=12)
BEGIN
   INSERT INTO @months(MonthNum)
   SELECT @i
   SET @i=@i+1
END

SELECT * FROM @months
				

Once we execute the above, we can see the output results will all the month numbers as shown in the below image.

sale report matrix Sql

Modifying the Existing T-SQL Query:

The next step is to modify the existing report query to include the table variable @months. Using a LEFT JOIN to join the @months table to the FactSalesQuota table, in this case even if the main data table is missing data for few months we will still get all the months in the output, but the rest of the columns, other than MonthNumber, will have NULL values.

DECLARE @months table (MonthNum int)

DECLARE @i int = 1 
WHILE (@i < = 12) 
BEGIN 
   INSERT INTO @months(MonthNum) 
   SELECT @i 
   SET @i = @ i + 1 
END 

SELECT 
   b.FirstName + ' ' + b.LastName as EmployeeName 
   ,[SalesAmountQuota] 
   ,m.MonthNum  as CalenderMonth 
   ,MONTH([Date]) as CalenderMonth_old 
   ,YEAR([Date]) as CalenderYear 
FROM @months as m 
LEFT JOIN [dbo].[FactSalesQuota] as a on m.MonthNum=MONTH(a.[Date]) and YEAR([Date])=@Year 
LEFT JOIN [dbo].[DimEmployee] as b on a.EmployeeKey=b.EmployeeKey 				
				

After running the report, we can now see the matrix columns for all the months as shown in the below image.

sale report matrix Sql with month

Acknowledgement:

This Tutorial is taken from Display column headers for missing data in SSRS matrix report

Related Tags

About

32 Tutorials
27 Snippets
6 Products

More

Contact Us

Contact us

Stay Connected