locked
SSRS Reports exporting to excel in multiple pages RRS feed

  • Question

  • Hi,

    We have an SSRS Report where in we configured paging with 20 records per page. Everything is fine.  Now, when we try the report to export to Excel option, it exports 20 records per sheet. 

    Is there any way, we can get all the records in a single sheet, without affecting the paging?

    Thanks,

    PVSAVSG.


    Thanks, PVSAVSG.

    Tuesday, December 18, 2012 9:45 AM

Answers

  • Hi PVSAVSG,

    Which version of Reporting Services are you using? If you use SSRS 2005 or SSRS 2008, I agree with Uri that this cannot be achieved. However, if you use SSRS 2008 R2 or SSRS 2012, we can resolve this issue by adding page breaks to the group based on an expression.  

    If you have added page breaks to the row group between instance instance of the group not based on any exrepssion, you can use the following steps to resolve the issue:

    1. Press F4 to open the Properties pane, and click the row group that you have added page breaks to from the Row Gruops pane.
    2. In the Tablix Member Properties pane, expand Group -> PageBreak.
    3. Set the BreakLocation option to "Between", and set the Disabled property to "=IIF(Globals!RenderFormat.Name="EXCEL",True,False)".

    If you have add page breaks using the method described in the thread (http://social.msdn.microsoft.com/Forums/pl/sqlreportingservices/thread/969eb90b-f641-4440-bc95-0fac7d4aad18), you can modify the expression as follows:

    =IIF(Globals!RenderFormat.Name="EXCEL",True, IIF(RunningValue(Fields!FieldName.Value,Count,"Tablix1") MOD 20=0, False, True))

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Shahfaisal Muhammed Wednesday, December 19, 2012 1:19 PM
    • Marked as answer by Mike Yin Sunday, December 23, 2012 2:10 PM
    Wednesday, December 19, 2012 1:08 PM

All replies

  • I do not think it is possible (at least on SS2005) , remove page breaks and then you will be able.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, December 18, 2012 10:10 AM
  • Hi PVSAVSG,

    Which version of Reporting Services are you using? If you use SSRS 2005 or SSRS 2008, I agree with Uri that this cannot be achieved. However, if you use SSRS 2008 R2 or SSRS 2012, we can resolve this issue by adding page breaks to the group based on an expression.  

    If you have added page breaks to the row group between instance instance of the group not based on any exrepssion, you can use the following steps to resolve the issue:

    1. Press F4 to open the Properties pane, and click the row group that you have added page breaks to from the Row Gruops pane.
    2. In the Tablix Member Properties pane, expand Group -> PageBreak.
    3. Set the BreakLocation option to "Between", and set the Disabled property to "=IIF(Globals!RenderFormat.Name="EXCEL",True,False)".

    If you have add page breaks using the method described in the thread (http://social.msdn.microsoft.com/Forums/pl/sqlreportingservices/thread/969eb90b-f641-4440-bc95-0fac7d4aad18), you can modify the expression as follows:

    =IIF(Globals!RenderFormat.Name="EXCEL",True, IIF(RunningValue(Fields!FieldName.Value,Count,"Tablix1") MOD 20=0, False, True))

    If you have any questions, please feel free to let me know.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Shahfaisal Muhammed Wednesday, December 19, 2012 1:19 PM
    • Marked as answer by Mike Yin Sunday, December 23, 2012 2:10 PM
    Wednesday, December 19, 2012 1:08 PM