Paging in SSRS report with huge result set

• SQLCurious

  SSCrazy

  Points: 2339

  More actions

  May 29, 2013 at 5:19 pm

  #394875

  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

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  May 29, 2013 at 5:41 pm

  #1619527

  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[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  May 29, 2013 at 7:04 pm

  #1619540

  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
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

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