Freeze Excel Column Header for SQL Server Reporting Services Report

By:   |   Comments (14)   |   Related: > 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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




Thursday, June 9, 2022 - 11:30:49 AM - Mark G Back To Top (90150)
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.

I have two Tablix's and it works great.... but I need the export to be on different tabs. This functionality did not work when I did what you suggested above. Any ideas?

Tuesday, November 30, 2021 - 4:50:25 AM - Anand M Back To Top (89502)
If you remove the tablix header and add it in report header, you can see those columns like a freeze in Excel file when you export. But, how can you add Filter(Ctrl+Shift+L) in that exported Excel file ?

Monday, July 20, 2020 - 5:14:39 PM - ross Mason Back To Top (86160)

How do you export a frozen column header out to Excel as opposed to a row header.

Monday, July 20, 2020 - 9:12:27 AM - Christy S Back To Top (86157)

This worked perfectly when exporting to Excel. However, when the Excel file is viewed on a mobile device, the coulmn headings overlap on each other and are unreadable. Any suggestions?

Friday, January 25, 2019 - 9:58:01 AM - Ryan Lampert Back To Top (78882)

 Extremely helpful!  I spent so much time trying to format before finding this tip.  Thanks so much!!!

Wednesday, January 17, 2018 - 4:05:52 AM - Sanjeev Back To Top (74972)

 

 I tried this but found a new issue though  Freezing works for me,The header consuming more cells like I want ID on cell A BUT appears on B, SAME LIKE IF I have kept colulmn width larger then it consumes 4-7 columns in excel and opening of excel takes lots of time. If you have solution kindly get back at soonest.And more I want to apply filter is that possible ?

 

Wednesday, August 2, 2017 - 1:49:34 PM - Prasad Back To Top (63791)

Hi Ganesh - Thanks a lot for shaing your knowledge on the SQL Server BI Reports.  I have one question, is there a way to enable filter on the columns in excel when you export the data into excel?  Similar to the way you have made the columns headings froze, I would like to have filter enabled on them.

Thanks a lot for your time

Prasad

 

Friday, June 30, 2017 - 4:43:54 PM - Minoshka Back To Top (58635)

 Great!! Thank you so much! I was able to create my report perfectly.

 

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

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 (43831)

 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 (43639)

 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 (38298)

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 (38259)

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 (38258)

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.














get free sql tips
agree to terms