4

I have a stored procedure that return bulky records. Accordingly on showing records in reporting service, it waits until all data were fetched then show them.

I wonder if there are any ways to show records that currently fetched and other record append to pages and be ready to show, thus user can see report at once.

EDIT: I search into reporting service and found snapshot feature. Though this help performance of reporting but it's a typical offline.

It's interesting that this problem was't before on Crystal report 6 or older. Same in Sql Server 2000 when we executed a stored procedure, It show results Immediately but in new Sql Server 2005 and upper results not shown until all of them fetched!

Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69
Siamak Ferdos
  • 3,181
  • 5
  • 29
  • 56
  • the visible report area only sets itself up after all the datasets in the report have been executed successfully. so it will, in all cases, wait for all the data to be retrieved. – KrazzyNefarious Aug 20 '15 at 05:43
  • Do you need to show all the data or could you specify a parameter which the user selects to limit the data to a subset. – Steve Ford Aug 24 '15 at 14:17
  • @SteveFord I cannot do this for two reason, first I want make reports for an existing application and its stored procedure are ready and I can't change them and second one is that I want to find a way to do this. It'll be useful for me and may be for others to work easier by largish data – Siamak Ferdos Aug 25 '15 at 04:23

1 Answers1

3

The short answer is no. SSRS data sets do not support read calls or partial retrieval of result sets. They call an execute and retrieve the full result set before initializing report rendering. When generating large reports it is generally not recommended to run them on demand unless they support pagination. However pagination will only limit the data sent between the SSRS service and the browser, not between the SSRS service and the database. SSRS will still wait for the entire data set get SP to be returned before starting to render the first page.

The simplest solution is to split the data set into multiple parts, add parameters to the get SP to return smaller data sets. Or you could try passing a page number to the SP and use a sub report on each page.

This article is worth a read.

Here are 4 basic strategies you can use for large reports.

  1. Use pagination. The server will send the report to the browser one page at a time. This option only works if you are rendering in a browser or report viewer. It would not work for instance if you are downloading an excel file on demand, that would require the completion of all the queries before sending the file to the client.
  2. Configure a subscription to distribute the report via email or to a shared folder on a fixed schedule.
  3. Configure report snapshots.
  4. Use sub reports to break a large query into multiple smaller parts (not recommended).

Other features of reporting services you might want to look into.

  1. Shared dataset cache
  2. Cache a report

Update:

Pagination can be setup by adding page breaks or grouping in a table. This will enable SSRS to return the first page to the browser while the other pages are still being rendered. However it should be noted that pagination will not effect the call between your db and SSRS server. If you have one large query the the first page rendering will not start until that result set has been completely loaded from the DB.

Sub reports allow you to return a query for each page in your report. For instance if you have a query that returns 100 rows and you wanted to show only 20 rows. Use the RANK() OVER to group your results in to groups of 20,then return the group numbers, these will be your page numbers. Set this as the main query for the report and format your pagination to create one page per page number returned from the main query. Then create a sub query sp that accepts your page number and number of rows you want to show, pass that page number from the main query. The sub query will return the 20 records you want to show on that page.

Why sub report as paging not recommended? Using sub queries like this is not recommended because it will be difficult to update or maintain, plus with all the extra queries, grouping, and table aggregation in the SQL, it would be much less efficient, maybe even take longer that your original query. For large queries it is recommended to use subscription, a cache or snapshots.

Community
  • 1
  • 1
Padhraic
  • 5,112
  • 4
  • 30
  • 39
  • I read about `Use pagination` but I couldn't find how can I send one page per time to browser. Can you guide me how should I do this? – Siamak Ferdos Aug 24 '15 at 03:57
  • How are you rendering the reports? excel? pdf? windows form Report Viewer Control? displaying the report in a browser window? – Padhraic Aug 24 '15 at 04:01
  • displaying the report in a browser window – Siamak Ferdos Aug 24 '15 at 04:30
  • if you are displaying in a browser the paging controls should appear in the upper left of the browser window automatically if your report is setup to support it. I have updated my answer with another option for implementing this with sub reports. – Padhraic Aug 24 '15 at 05:24
  • As I understood, paging and breaking is for setup of our page after all data fetched. I see pager number at upper left of page. But in large data User should waits 1 min, 2 min , ... to load all data then use paging. I want to eleminate this waiting if it's possible – Siamak Ferdos Aug 24 '15 at 05:42
  • Apart from using sub reports (see my update to my answer) to trick SSRS into supporting paging at the query level there is not way to do this naively at the data set level. The only other alternative is to use snapshots, subscriptions or redesign your report to be smaller. – Padhraic Aug 24 '15 at 05:51
  • Just to point out one small missing item from answer: SSRS will wait until render is complete and only then sends data to client (or "to" a subscription). This is why SSRS doesn't like large data sets. – AcePL Jan 20 '20 at 09:28