This is a common issue in reports, if you are working on generating reports with several hundred thousand records and complex joins. The general solution for this problem would be reducing the time that report takes to run by simplifying the report or improving performance of stored procedures if used. But this is not always possible often with our rigid requirements. For these situations following two solutions can be helpful;
- Run the report offline – This can be done by scheduling report to run on free hours like night. Then the users only see this snapshot of the data. But this approach may not be enough flexible, as we cannot directly change parameters for each run.
- Deliver the reports via a subscription – Here user has to come to the browser and subscribe to the report with parameters if needed. Then can leave the page without waiting until the report is generated. Report will be delivered to the person via email or a file share.
To brief the Subscription method, it is a request to deliver a report at a specific time or in response to an event rather than running a report on demand. There are two types of Subscriptions supported by SQL Server Reporting Services.
Standard Subscriptions – Usually created and managed by individual users and they consist of static values that cannot be varied during subscription processing.
Data-Driven Subscriptions – This is a dynamic subscription. Here the presentation, delivery, and parameter values are retrieved at run time from a data source. Data-driven subscriptions are a good choice if you want to vary report output for each recipient.
A subscription consists of several parts, which we should specify. They include the report that should run, report delivery method, the rendering format, conditions for processing the subscription, and parameters used when running the report.
For more information visit official msdn site at following locations,