Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Paging in SSRS report with huge result set Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 5:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:21 PM
Points: 76, Visits: 291
I have a report that returns a big result set (100s of pages long). Is there a way to make the first page generated (rendered) first for faster response time? The next page should be rendered only when the user tries to navigate to it.

I am sure someone must have done this before. Can anyone shed some light on this, please?

- Rex
Post #1457999
Posted Wednesday, May 29, 2013 5:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 5,401, Visits: 7,513
Not directly, at least not with SSRS. You'd need more of an interface to pass down parameters for rowcounts and starting rows to a proc which would restrict the data being passed up. ASP.NET or something similar.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1458002
Posted Wednesday, May 29, 2013 7:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,796, Visits: 5,799
You might be able to put page navigation buttons on the report (clickable images) that call the same report with a specific page number and have the paging logic in your SQL query (Stored Procedure).

BUT: you would not be able to export the report to excel / pdf etc and get anything more than the current page.

The buttons would have a "Go to Report" action and have logic a bit like this in the expression for the value of the "Page number" parameter of your report.

The "Page back" button:
=IIf(First(Fields!Page.Value, "DataSet1")>1,First(Fields!Page.Value, "DataSet1")-1,1)

The "Page forward":
=First(Fields!Page.Value, "DataSet1")+1

Of course, you would need to include the page number in your dataset for this to work and your Query would need good fast paging code to make this worthwhile.

This does work, I have used it myself.



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw



  •   Post Attachments 
    Navigation.PNG (24 views, 23.87 KB)
    Post #1458017
    Posted Thursday, May 30, 2013 2:18 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Tuesday, September 9, 2014 12:21 PM
    Points: 76, Visits: 291
    Thanks for your suggestions. My query is kinda notorious, and that is the reason I am looking to choose/render data in chunks. I guess I don't have many options

    Thanks anyway.

    - Rex
    Post #1458456
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse