The short answer is no. SSRS datasets do not support read calls or partial search for result sets. They invoke execution and get a complete set of results before initializing report rendering. When creating large reports, it is usually not recommended to run them on demand if they do not support pagination. However, pagination limits only the data transmitted by the SSRS service and the browser, and not between the SSRS service and the database. SSRS is still waiting for the entire dataset to receive the SP to return to the beginning of the first page.
The simplest solution is to split the data set into several parts, add parameters to get SP to return smaller data sets. Or you can try to pass the page number to the SP and use an auxiliary report on each page.
This article is worth reading.
Here are four basic strategies you can use for large reports .
- 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 in the report editor. This will not work, for example, if you download the Excel file on demand, which will require completion of all requests before sending the file to the client.
- Set up a subscription to distribute the report by email or to a shared folder on a fixed schedule.
- Customize report snapshots .
- Use auxiliary reports to break a large query into several small parts (not recommended).
Other reporting services features you might want to learn.
Update:
Pagination can be customized by adding page breaks or grouping in the table . This will allow SSRS to return the first page to the browser while other pages are still displayed. However, it should be noted that pagination does not affect the call between your db server and the SSRS server. If you have one big request, the rendering of the first page will not start until this result set is fully loaded from the database.
Counting allows you to return a query for each page of your report. For example, if you have a query that returns 100 rows, and you want to show only 20 rows. Use RANK () OVER to group your results into groups of 20 , then return the group numbers, these will be your page numbers. Define this as the main report request and format your pagination to create one page per page number returned from the main request. Then create an extra sp request that takes your page number and the number of lines you want to show, pass that page number from the main request. The sub query will return 20 records that you want to display on this page.
Why is it not recommended to report signatures as paging? Using subqueries like this is not recommended because it will be difficult to update or maintain, as well as with all the additional queries, grouping and aggregation of tables in SQL, it will be much less efficient, maybe even take longer than your original query. For large requests, we recommend using a subscription, cache, or snapshots.