Introduction
SQL Server Reporting Services (SSRS) is a server based scalable and extensible platform for authoring, deploying, executing and managing reports based on a variety of data sources. SSRS allows us to create interactive, tabular, graphical (using data visualization controls) or free form reports from relational, multidimensional (using MDX or DMX) or XML data sources. Furthermore it allows you to view/export your reports in a variety of formats. You can enable report caching, which improves the performance for subsequent requests if the parameters remain same.
You can set SSRS to create report snapshots, which show data at a point of time or you can subscribe to your published reports. This article explores the report snapshot feature in detail.
Understanding Report Snapshot
"A snapshot represents a state/data at a particular/specific point of time."
An SSRS report snapshot represents a report that contains data retrieved at a specific point of time along with layout information in the form of intermediate rendering format. SSRS allows you to create report snapshots on defined schedule or on demand whenever you need to; a report snapshot is stored in the ReportServer database.
Generally you set the time (or define the schedule) to create a report snapshot during off peak hours, which reduces the load on your database servers during peak hours. Later on, users can view these reports either during peak hours or off peak hours (as reports are served from the created snapshots). SSRS can create as many report snapshots as you want or it can create a limited number of snapshots in which case the oldest one is dropped when a new report snapshot is created. You should choose to limit the number of report snapshots to be maintained to minimize the storage requirement for ReportServer database.
There are some constraints to be taken care before you setup report snapshot.
1. A report snapshot can be created only if the credential to connect to the data source is stored with data sources. A report with data sources, which use Windows Integrated security or prompts for a credential will not let you to create a report snapshot as you can see in the image below.
Figure 1 - Specifying Credential
2. You need to have all the query parameters set to have default values (the reason is obvious, SSRS creates a report snapshot without any interaction and needs default values to pass to data source for query execution) or else it will not let you to create a report snapshot as you can see in the image below:
Figure 2 - Specifying default parameters
To specify the default values for query parameters, go to the report management page, click on the parameters tab on the left and on the right side you can define the default values for parameters as shown below.
Figure 3- Specifying default values for parameters
Getting Started With Report Snapshot
To setup report snapshot creation, go to Report Manager, navigate to the folder that contains report, point to the report you want create or set up a snapshot, click on the down arrow and click on Manage menu item as shown below.
Figure 4 - Managing a Report
On the report management page, click on Snapshot Options on the left and you will see the screen shown below.
Figure 5 - Snapshot Options Page
Now, how will reports be served/rendered from the snapshot? For each report, you need to specify the report execution properties. To do that, click on Processing Options on the left side of the manage report page as shown below and choose one of these options:
Figure 7 - Processing Options Page
- Always run this report from the most recent data – You can choose this option if you want to render your report from the most recent data from the source for each request separately or from the report cache to enhance the performance. (If you have enabled report caching, refer my other article, Report Caching in SQL Server Reporting Services 2008 R2, to learn more).
- Render this report from a report snapshot – When you choose this option, your request for report will be rendered from the report execution snapshot. If not done before, you can also define the schedule to create the snapshot here.
There are a couple of caveats that need to be mentioned when enabling/utilizing report snapshot:
1. Report snapshots are stored in ReportServer database and hence you need to plan for additional storage requirements for this database. Though you can minimize it by limiting the maximum number of snapshots to be maintained at a time in the report history.
2. All the data sources used in the report must have credentials saved with it or with no credentials.
3. All the query parameters of the report must have default values defined for it.
4. Schedule report snapshot to be created during off peak hours.
5. A report served / rendered from report snapshot represents the data when the snapshot was created and might not be up to date.
6. Report snapshots are created for two main purposes, first to reduce the load or processing time on the database server during peak hours by creating report snapshots during off peak hours and second, to create report history (to see/analyze how data has changed over time) either by weekly, monthly, etc.
Conclusion
In this article I talked about how SSRS improves the performance of report rendering by utilizing the report snapshot, how to configure a report snapshot and how to define report execution properties to render the report from the report execution snapshot.
Resources
Setting Report Processing Properties
Report and Snapshot Size Limits
See all articles by Arshad Ali
» 
Comment and Contribute
