Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Freeze Excel Column Header for SQL Server Reporting Services Report


By:   |   Read Comments (6)   |   Related Tips: > Reporting Services Formatting

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.

Adding New Report

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.

Report Data Pane Window

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.

Adding Tablix

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

Report Preview Without Column Freeze

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.

Copy Tablix Header Textbox In Header

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

Report Header Textbox Proper Arrangement

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

Delete Tablix Header Row

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

Tablix After Modification

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.

Report Preview In Excel Column Freeze

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.


Last Update:






About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Saturday, January 28, 2017 - 10:24:13 PM - Anu Back To Top

Hi,

When I export SSRS report to excel, I don't see filters on the top row. I manullay have to add them in excel every time I export the report. Is there any way I can have it auto populated when I export SSRS report to excel?

Thanks!


Tuesday, November 22, 2016 - 3:12:10 PM - Bob Back To Top

 If you want to filter in Excel, do not delete the Column Header row.  Just Delete the words and make that row alittle narrower.

The first Excel page will have a narrow row you can filter from and that row disappears in subsequent pages.

 


Wednesday, October 26, 2016 - 8:25:13 AM - Rahul Kale Back To Top

 Hello,

I guess this will not work of you want to apply filter in excel.

 


Tuesday, July 28, 2015 - 10:34:43 AM - J Back To Top

Never thought of placing the column headers in the report header, works great. In fact, when exporting to Excel, the column headers appear as a fixed header in the worksheet instead of being hard-copied in-between data at every page break - this is perfect behaviour.

There is just this one last thing: a group header. The group requires several pages to print all its record and so far I have been unable to get the group to be also printed at the top of every page. It is not possible to put the current group "value" in the header. The tablix properties "Repeat row headers on every page" and "Keep header visible while scrolling" and the same for columns just plain do not work either. SSRS 2008 R2.

 Your thoughts on this ?

 

 


Monday, July 20, 2015 - 11:06:59 AM - Ghanesh Back To Top

Kris Maly,

Thanks for following my tips and I really appricate your suggestions. I will definitly try to improve my writing skills. :)

Regards,

Ghanesh


Monday, July 20, 2015 - 10:08:09 AM - Kris Maly Back To Top

I enjoyed reading this article/Tip and recommend others.

I followed all the steps and created the sample report and it works fine.

I would like to let you know when you mentioned "copy Text Boxes" it gave me little confusion. I think you should have said "copy Tablix Header Text Boxes"

Another point you said Add Report Header. I think it should be Page Header since I added Page Header and did it worked fine.

I think you should said a ststement on Alignment of Text Boxes and Tablix to Top most and left most edge would export the data without generating the extra ROW and COLUMN on Excel. 

Nevermind those are all small wordings but all your steps are RIGHT and it's WORKING FINE.

Thanks for educating the community and appreciate your volunteership/efforts.


Learn more about SQL Server tools