export ssrs to excel as 3 sheets? RRS feed

  • Question

  • Hi experts,

    I have 3 datasets, but the report is not going to be used in ssrs, the user will only export it to excel...

    How can I force that when the user exports it to excel, for the report to put each dataset on a diferent excel sheet...?

    Wednesday, May 23, 2018 10:55 AM

All replies

  • You can create three tables inside your report each pointing to different dataset

    then set page break for each tables by choosing Add a page break after option or by setting corresponding property value for  BreakLocation property

    Then give PageName property of the table suitable values. This will come as sheetname for the Excel file. You can make it static or write an expression based on dataset field for dynamic names

    Then while exporting to Excel, SSRS will export it to multiple sheets with names based on the expression/static value you set

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, May 23, 2018 11:07 AM
  • Hello,

    Add a rectangle between the tables and add the wanted sheet Name to the "PageName" + set "PageBreak" property of the rectangle.

    If it's one table then see https://www.mssqltips.com/sqlservertip/3527/export-sql-server-reporting-services-report-data-into-multiple-excel-worksheets/

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, May 23, 2018 11:07 AM
  • Hi maca128

    You could add “Pagebreaks” after each data region which contains different datasets.

    You could see more information at: Reporting Services export to Excel with Multiple Worksheets

    Thanks for understanding.

    Best Regards,

    Eric Liu

    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    • Proposed as answer by Mitarai Queen Wednesday, May 30, 2018 5:12 AM
    Thursday, May 24, 2018 2:06 AM