SSRS report exports to excel file

Posted on 2011-04-20
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
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    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.
    LVL 1

    Accepted Solution

    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.
    LVL 1

    Author Comment

    FYI, I used the script task in the SSIS package
    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
    Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now