Freeze Excel Column Header for SSRS Report

Problem

You created a SQL Server Reporting Services report and the requirement is to output to Excel format. As you may know, SQL Server Reporting Services (SSRS) provides functionality to export a SSRS report into Excel, but for long reports the column headers disappear in Excel when you scroll through the data. In this tip we look at how to freeze the column headers in Excel when the report is created using SSRS.

Solution

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report.

In this tip I will describe how to freeze the Tablix column headings in SSRS, so that when viewing the data in Excel the column names can be seen even after scrolling down. SQL Server Reporting Services doesn’t provide any out of box functionality to freeze the column headings when exporting to Excel, so we will show a workaround to do this.

Keep Headers Visible When Scrolling Through an SSRS Report

You might think the easy way to fix this is repeat the column headings on each page which can be done as shown below.

1. In the grouping pane, make sure to turn on Advanced mode (click on the small black down arrow on the far right of the grouping pane).
2. Select the corresponding Static item in the row group hierarchy which has the column heading.
3. Press the F4 key in the properties grid and it will open the properties window.
4. Set RepeatOnNewPage property to True, KeepwithGroup property to After and Fixed Data property to True.

This method will repeat the Tablix column heading on each page and will show the Tablix column header while scrolling down the report, but this method will not freeze the Tablix column heading when the report is exported to Excel. This method also works when you preview the report in Browser/Report Builder or BIDS environment.

Keep Headers Visible When Scrolling Through a SSRS Report in Excel

We can create a workaround to solve this problem. To demonstrate the solution, I will use the AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Step 1: Create New Report

Let’s create a sample report for demo purposes. Please change the report name to Freeze_Tablix_Header. You can refer to the below image.

Step 2: Create Data Source and Dataset

I created a data source connection to AdventureworksDW2008R2 sample database and created the below dataset for this sample report.

Main Dataset: This dataset will be used for the report body and it returns 5 data fields.

SELECT 
FirstName, LastName, MaritalStatus, Gender, EmailAddress
FROM  DimCustomer

After creating the Main dataset, the Report Data Pane looks like the below image.

Step 3: Add Tablix

Let’s add a Tablix for data viewing purposes. I will pull all 5 data fields from the Main Dataset into the Tablix. After adding the Tablix, the sample report looks like the below image.

If you preview the report and export it into Excel you will find that the Tablix column headers are not visible after scrolling.

Step 4: Freeze Tablix Column Headers

To freeze the headers, first add a Report Header in your report and copy each Textbox from the Tablix Row header to Report Header as shown below.

Arrange the new Textboxes and align with the data columns as shown below.

We can then delete the Tablix row header since we don’t need to show the column headings twice.

After the above modifications, the report should look like the below image.

Step 5: Report Preview

We have done all the necessary changes, now let’s preview and export the report into Excel. As you can see from the below image, when you scroll the report headers can be seen in Excel.

Conclusion

This approach is useful in the following conditions:

• When a report has a single Tablix.
• When a report has multiple Tablixes, but each Tablix is horizontal to each other so the Tablix header can be put in the Report header

.

Next Steps

• Check out how to export a SSRS report into multiple Excel sheets with dynamic sheet names
• Check out other tips on SQL Server Reporting Services Parameters.
• Check out all SQL Server Business Intelligence Tips and Tricks.
• Check out all of my tips.

Ghanesh Prasad

Ghanesh Prasad is a Microsoft BI lead developer for an industry leading Indian IT Service Company in Bangalore. He has over 5 years of work experience with Microsoft Business Intelligence and expertise in SQL Server, SQL, T-SQL, SSRS, SSIS, SSAS, and MDX languages. In his organization he is responsible for developing end to end data loading solutions, cube development, report development, migration, testing, deployment and maintenance.