none
Export Multiple Reports to Excel

    Question

  • Hi all,  This is my first time visiting this forum and wasn't able to find an answer to my question so I figured I would post it here.  I have a project created in Reporting Services 2008 with approximately 60 reports that contain various tables and graphs.  I need to present these reports in a single Excel workbook with each report as a separate tab.  I am hoping there is a better way to go about doing this rather than loading each report, Previewing, Refreshing and doing the Export.  Is there a way to create a batch to refresh and export all the reports? Even if it does them all to a separate workbook I can then run an easy Macro in Excel to combine them... so I just need a quick way to get them out of Reporting Services.  Any insight would be great.  Thanks a lot!
    Monday, April 06, 2009 1:16 PM

Answers

  • Hi eschine,

     

    For your requirement, you can achieve the goal by using the following solution:

    1.       Use the tool RS Utility to export each report to separate workbook.

    a)         Create a script file to get the list of the reports, save the list to a XML file. Let say ExportReportsList.

    b)         Use the RS Utility to execute the script file to create the folder.

    Here is a sample for running the script file:

    rs -i ExportReportsList.rss -s http://myserver/reportserver

    c)         Create another script file, let say ExportReports, read the XML file, and then use the name of the report that stored in the XML file to export the report to EXCEL.

    d)         Use the RS Utility to execute the script file to create the folder.

    Here is a sample for running the script file:

    rs -i ExportReports.rss -s http://myserver/reportserver -Exec2005

    2.       As you mentioned, combine each workbook to a worksheet in a single workbook using Macro.

     

    Here are some helpful articles for your information:

    ReportingService2005: http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.aspx

    rsUtility: http://msdn.microsoft.com/en-us/library/aa179659(SQL.80).aspx

     

    If you have any more question, please feel free to ask.

     

    Thanks,

    Jin


    Jin Chen - MSFT
    Wednesday, April 08, 2009 10:16 AM
    Moderator

All replies

  • You can skip the preview and refresh and manual export to Export by opening the reports by URL and setting the rendering parameter. This would require copy/pasting the URL's and putting the Excel parameter behind it to open them, but its faster than doing this manually.

    Example:
    http://myserver/reportserver?/Sales/YearlySalesSummary&rs:Command=Render&rs:Format=Excel

    In addition, you could create a new main report and put 60 subreports on it for each report you have. After each subreport, but a rectangle object behind it and set a pagebreak on this rectangle. Then open the main report and export this to Excel. I'm not sure whether RS2008 supports exporting subreports to Excel though (this doesn't work in RS2005 and I haven't checked it in RS2008 yet).

    Monday, April 06, 2009 1:43 PM
  • If you are going to be doing this often,  use the scheduling feature.
    Monday, April 06, 2009 2:33 PM
  • Thanks for the reply guys.  This is something that I don't think needs to be scheduled as it should be a one time export.  It seems the fastest way is to use the sub-report page break option... the only downside is that it doesn't name the tabs with the report names.  Do you know of any way to make that happen?  Thanks for all the advice so far... it's been helpful!
    Monday, April 06, 2009 4:20 PM
  • Hi eschine,

     

    For your requirement, you can achieve the goal by using the following solution:

    1.       Use the tool RS Utility to export each report to separate workbook.

    a)         Create a script file to get the list of the reports, save the list to a XML file. Let say ExportReportsList.

    b)         Use the RS Utility to execute the script file to create the folder.

    Here is a sample for running the script file:

    rs -i ExportReportsList.rss -s http://myserver/reportserver

    c)         Create another script file, let say ExportReports, read the XML file, and then use the name of the report that stored in the XML file to export the report to EXCEL.

    d)         Use the RS Utility to execute the script file to create the folder.

    Here is a sample for running the script file:

    rs -i ExportReports.rss -s http://myserver/reportserver -Exec2005

    2.       As you mentioned, combine each workbook to a worksheet in a single workbook using Macro.

     

    Here are some helpful articles for your information:

    ReportingService2005: http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.aspx

    rsUtility: http://msdn.microsoft.com/en-us/library/aa179659(SQL.80).aspx

     

    If you have any more question, please feel free to ask.

     

    Thanks,

    Jin


    Jin Chen - MSFT
    Wednesday, April 08, 2009 10:16 AM
    Moderator