SSRS report exports to excel file

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?
LVL 1
johnnyactionAsked:
Who is Participating?
 
johnnyactionAuthor Commented:
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.
0
 
Nico BontenbalCommented:
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.
0
 
johnnyactionAuthor Commented:
FYI, I used the script task in the SSIS package
0
 
johnnyactionAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.