Display column headers for missing data in SSRS matrix report

By:   |   Comments (4)   |   Related: > Reporting Services Formatting


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.

Current Report - Description: Here is the report where you can see missing months.

The expected output is shown below:

Expected report - Description: here is the report output we are exoecting

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.


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.

   b.FirstName+' '+b.LastName as EmployeeName
   ,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)
   INSERT INTO @months(MonthNum)
   SELECT @i
   SET @i=@i+1

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.

Tablevariable - Description: Results from table variable

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) 
   INSERT INTO @months(MonthNum) 
   SELECT @i 
   SET @i = @ i + 1 

   b.FirstName + ' ' + b.LastName as EmployeeName 
   ,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 

The next step is to update the report data set query with the above updated T-SQL code and then refresh the fields as shown in the below image.

Querydesigner - Description: Refresh the feilds by updating with new SQL Query

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

After fixing the Query - Description: Here is the report output right after fixing the code.

However, there are couple of things we need to fix in this report.

We want to remove the records where Employee Name has no data. To do this we can remove blank employee rows by writing a simple filter expression on the Employee row group section in the SSRS report as shown below.

ISNOTHING(Fields!EmployeeName.Value) = FALSE

Group filter - Description: here is the row filter expression for filtering the data.

We also want to display 0ís instead of blank values.  This can be done by writing an IIF expression on the value text box as shown in the below image.

Value filter - Description: Value filter expression to display blank values to 0

Final Report

After fixing the above two formatting items, we can rerun the report and now the report displays as needed.

final report - Description: Here is the final report , after fixing these items
Next Steps
  • When you have the same or similar requirement you can follow these steps to develop a solution using T-SQL code and SSRS expressions.
  • As you can see, we can easily display column headers even when data is missing by doing a few workarounds in our T-SQL code and formatting.
  • Download the SSRS rdl file for this report

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Amar Reddy Amarendra Reddy Thummeti is a Senior Business Intelligence Engineer who works in Silicon Valley and holds a Master's Degree in Computer Science.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, May 31, 2019 - 10:56:15 AM - Mark Nations Back To Top (81277)

Hello. I am new to SQL/T-SQL and I found this article very helpful. However, I need to take it one step further: How can I query/script a way to flag rows & columns with missing data, possibly creating a new column that marks the present data as "Good," and missing data as "Bad?"

Thank you for your assistance!

Mark Nations

Optimization Developer

BPX Energy

Thursday, January 31, 2019 - 11:02:27 AM - Jeffrey R Green Back To Top (78925)

I finally found what i think i need . . . and i am SO close . . . . i really could use a professional set of eyes on this.

Please let me know if you can somehow assist me.

Friday, December 7, 2018 - 1:08:24 AM - Jesse Back To Top (78422)

Hi Amarendra

Your solution is straightforward and it works well, but I've found if you try to adopt this exact same method in a stored procedure reference, it fails to work as expected and ends up like your first screenshot..

Any idea why this is?



Thursday, May 18, 2017 - 2:45:35 PM - John Back To Top (55854)


 This is so helpful. thank you Amarendra Reddy Thummeti.

get free sql tips
agree to terms