SSRS report exports to excel file

Posted on 2011-04-20
Medium Priority
Last Modified: 2012-05-11
Is there anyway when I export my report to excel from Report Manager to a directory on our intranet that I can have the column headers repeat on every page? I also need the column widths to auto size to fit the width of the column data. CAN THIS BE DONE?
Question by:johnnyaction
  • 3
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35438756
I Googled on
excel autofit ssrs
and I'm afraid there is no way to do the autofit. You'll have to determine the optimal size of the columns in the report. I know that's difficult because the length of the data will change. If the data is just a table you could use something different than SSRS to create the Excel sheet. For example create a button on the sheet that pulls in the data directly from SQL Server and then autfits the columns.

About saving to the intranet. You'll get a Windows "Save As" dialog. If there is no way to save to the Intranet folder from Windows, than it also can't be done from Report Manager. You'll have to save it first, and then upload it to the Intranet.
You could create a script that runs on the server and saves the report as Excel in the right folder. If your intranet is IIS this isn't too difficult.

Accepted Solution

johnnyaction earned 0 total points
ID: 35440878
I was able to create an SSIS package that will open the excel file and autofit the columns and freeze the header rows and then save it. . It was actually quite simple. Let me know if you need the code. Thanks for your reply.

Author Comment

ID: 35440886
FYI, I used the script task in the SSIS package

Author Closing Comment

ID: 35465178

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question