Working with Report Snapshots in SQL Server Reporting Services

By:   |   Updated: 2010-01-18   |   Comments (15)   |   Related: > Reporting Services Data Sources


Problem

We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?

Solution

A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database. When a user requests the report, they automatically get the snapshot version stored in the ReportServer database rather than executing the SQL queries to retrieve the data from the underlying data source. You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers. You can also create snapshots manually and select which snapshot version of a report that you would like to run. You can save as many snapshots as you like or you can have Reporting Services limit the number of snapshots that are saved. Besides reducing the load on your database servers, you can use report snapshots to archive reports at a particular point in time for later viewing; e.g. month-end reports, year-end reports, etc. Depending on how many report snapshots you keep, you could use a significant amount of space in the ReportServer database.

After deploying a report, you can specify your snapshot settings by using the Report Manager. There are three requirements that must be met in order to take advantage of report snapshots:

  • Enable scheduled events and report delivery in SQL Server Surface Area Configuration (SQL Server 2005 only)
  • Any data source used in the report must store the credentials for connecting to the database; a report snapshot can be created on a schedule so the data source cannot rely on using the credentials of the interactive user.
  • All query parameters must have default values; since a report snapshot can be created on a schedule, there is no interactive user to specify these parameter values.

In this tip we will walk through the following steps to show how to enable report snapshots:

  • Enable scheduled events and report delivery in SQL Server Surface Area Configuration (SQL Server 2005 only)
  • Configure a data source
  • Configure a report snapshot
  • Execute a report from a snapshot

Enable Scheduled Events and Report Delivery

If you are running Reporting Services 2005, launch SQL Server Surface Area Configuration from the Microsoft SQL Server 2005 / Configuration Tools program group. Click on Surface Area Configuration for Features then verify that "Enable scheduled events and report delivery" is checked as shown below:

surface area

Configuring a Data Source

As an example we will take a look at a SQL Server data source. Navigate to the folder in the Report Manager where your data source is deployed, and fill in the "Connect using" part of the page as shown below:

microsoft sql server

Note the radio button "Credentials stored securely in the report server" is selected, a user name and password are specified, and the "Use as Windows credentials" checkbox is selected. Whatever user name you choose must have at least read permissions on the underlying database; i.e. add the user as a member of the db_datareader database role for the particular database. In addition the user must have execute permission on any stored procedures that are used by the report; i.e. GRANT EXECUTE ON OBJECT::dbo.[stored procedure name goes here] TO PUBLIC(since the stored procedure only reads data you may want to give everyone access rather than just specific users).

Configuring a Report Snapshot

You configure a report snapshot at the individual report level. Navigate to the folder containing your report in the Report Manager. Click the Show Details button to display the detailed view as shown below:

snapshot example

Click the icon in the Edit column for the report you want to enable the snapshot. The Properties page will be displayed as shown below:

properties

Click on the Execution hyperlink to display the Execution Properties page for the report. Fill in the page as shown below:

configure

Click the radio button "Render this report from a report execution snapshot". From now on whenever a user requests this report, they will always get the latest snapshot. If a snapshot does not exist, one will be created and saved to satisfy the current request as well as future ones. You can specify to create a new snapshot based on a report-specific schedule or a shared schedule. A report-specific schedule is used only by this report, a shared schedule can be used by multiple reports. Select your schedule option and fill in the page as shown below:

schedule details

Return to the Properties page as shown above and click on the History hyperlink to display the History properties as shown below:

history properties

The following are the main points about the History properties:

  • "Allow report history to be created manually" will allow you to create a report snapshot on demand. You can select a report snapshot to view by clicking the History tab on the Properties page where a list of available snapshots will be displayed; you can click the New Snapshot button on this page to create a snapshot on demand.
  • "Store all report execution snapshots in history" will show the automatically generated snapshots in the history in addition to the scheduled snapshots.
  • "Use the following schedule to add snapshots to report history" will create snapshots per your schedule and add them to the history.
  • The default setting for the number of snapshots to keep is specified on the Site Settings page of the Report Manager.

There may be some confusion about specifying schedules for snapshots on both the Execution and History property pages. On the Execution properties page you are specifying when to refresh the report snapshot that is used to render the report when requested by a user. On the History properties page you are specifying when to create a report snapshot and add it to the history. You can navigate to the History property page and select which snapshot you want to view.

Execute a Snapshot Report

Let's take a look at the layout of the sample report that we will use to demonstrate the report snapshot:

cached report

The layout is intentionally simple; the textbox with the expression "Report Timestamp: " + Now provides a simple way to determine when the report snapshot was generated. On the Execution properties page I scheduled a new snapshot to be generated at 10:15AM each day. When I run the report I can see that the snapshot was generated at about that time this morning; e.g.:

sales report

Since I configured the History properties to add all snapshots to the history, I can navigate to the History tab and select a particular report snapshot to render as shown below:

snapshot history

Note that there are two reports. The first one was generated based on the schedule I specified on the Execution properties page;.this is the report snapshot that will be rendered automatically when someone requests the report. The second report snapshot was generated based on the schedule I specified on the History properties page. You can click on the hyperlinks to render these reports on demand.

Next Steps
  • The report snapshot feature in SSRS is the best kind; it's just a configuration setting on a deployed report. Keep this in mind when you're trying to increase performance on your database servers.
  • Report snapshots can be used to archive reports as of a certain point in time; e.g. month-end or year-end.
  • Report snapshots take up space in the ReportServer database so you should think about limiting the number of snapshots that you keep.
  • You can download the sample report and SQL scripts to create the test data here and use it as a basis for your own testing and experimentation.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-01-18

Comments For This Article




Sunday, June 16, 2013 - 7:28:23 PM - Raymond Barley Back To Top (25448)

Both caching and report snapshots work with parameters.  The actual parameters that were used when a report is cached or a snapshot is created are stored with the cache or snapshot.  When a report is requested the cache or snapshot will be used if the parameter values match a cached report or a report snapshot.  

If you have many combinations of parameter values, you have to run your report many times with various combinations of parameter values to force the cache or snapshot to be generated.  You can use a data driven subscription to generate a report with many different parameter value combinations.

You may be able to shorten your query time by creating some new tables to cut down on whatever is causing your queries to run for a long time; e.g. you may do some aggregations.  You may also be able to take advantage of indexed views where you create a view that your report uses and you add a clustered index on the view.  This essentially creates a table to support the view and the table is updated automatically as inserts, updates and delete happen.

Sunday, June 16, 2013 - 2:58:47 PM - Praveen Kumar Back To Top (25447)

Can I have parameters to a report which uses a snapshot? If Yes, Can I cache it for better performance? Basically, my question is related to this

We are facing a problem regarding Caching a report with multuple Multi Valued Parameters. More info about the problem here..

 

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/2de09264-ba07-4e32-a844-b451c0bb0b41

 

Prawin

Wednesday, April 3, 2013 - 5:50:00 AM - Ray Barley Back To Top (23133)

I'm not aware of any way to modify a snapshot.  

Tuesday, April 2, 2013 - 5:35:46 PM - Ramkumar Back To Top (23123)

Hi, I have a snapshot report defined and is being used for the past couple of years. Now I have a requirement to change one of the field name in the report and after changing all the new reports have the new field, but the old snapshots still have the old field name. Is there a way modify the snapshot and render using the new field? Or any programattic way of changing the snapshot would also be helpful. Thanks.

Monday, March 25, 2013 - 7:29:22 AM - philip webb Back To Top (22998)

create a blank field with the expression (where Sector is the parameter name) : 

 

=  "some text: " & join(Parameters!Sector.Value, " , " )

Tuesday, January 22, 2013 - 2:22:25 PM - Joanne James Back To Top (21624)

 

Ray - I am replying to your answer dated Jan 19: I did as suggested.  What appears to be currently happening is that the snapshot is not being returned when the user clicks on the report.  (before, it was just the header that was getting updated - now, the whole report is getting updated) .  I can go to report history and see the most recent snapsnot from there.  However, when clicking on report link, report returns more updated information. 

Saturday, January 19, 2013 - 12:51:30 PM - Ray Barley Back To Top (21574)

Create a report parameter that calls a stored proc to set its default value.  You can print the value of the parameter in the report header using a text box.  You could also pass the report parameter to your stored proc.

Since you will need a dataset for the report parameter default you could also just use a data table to print the parameter value(s).

Friday, January 18, 2013 - 6:30:13 PM - Joanne James Back To Top (21565)

Hi Ray.  I have a related question  - I want to show the parameter values that were used when the snapshot was taken on the report.   I can't seem to do this the way I currently have things set up. The stored procedure that is used for the dataset accepts null values currently, and if they are indeed null, changes them appropriately within the sp: from 5/1/2012 - day prior to current date.   I'm guessing this is part of the problem - and that I will need to provide the parameter values via the report parameters instead.  However, even if I do this, how do I get the parameter values that were used at the time of the execution to appear on the report? - do I have to put them in the data results somehow? 

 The user is not expected to re-run the report.  the report snapsot is to be taken  on Monday .  So I want users to see that snapshot during the week. 

 

Friday, November 23, 2012 - 8:41:51 AM - Ray Barley Back To Top (20488)

I did a quick check for SQL Server 2012; you can use the web service method CreateItemHistorySnapshot; check here for the details: http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.createitemhistorysnapshot.aspx

You can use the same method for SQL Server 2008 R2.  

Wednesday, November 21, 2012 - 4:18:47 PM - Mike Marsten Back To Top (20464)

Is there a way to programmatically kick off a new snapshot being generated?

I have an SSIS package that imports new data and then needs to auto-email a report based on that new data.

I can set up a a subscription to email the report to the users, but the package runs at different times so I cannot set up a time.

Friday, October 26, 2012 - 6:54:39 PM - Raymond Barley Back To Top (20109)

You can't.  If you set the processing option on the subreport to render from snapshot you will get the error

Error: Subreport could not be shown.

Friday, October 26, 2012 - 2:21:44 AM - Mangal Back To Top (20094)

How to use snapshots for subreport?

Tuesday, October 16, 2012 - 10:14:24 AM - Munish Back To Top (19939)

A very helpful article....

Works perctly in 2008 as well.

 

cheers

munish

 

 

Wednesday, September 8, 2010 - 7:02:08 AM - Ray Barley Back To Top (10135)
I don't think so.  I looked at the Reporting Services Web Service (for SQL Server 2008) and it has a method called CreateReportHistorySnapshot that only takes the report name as an input parameter.

Wednesday, September 8, 2010 - 2:48:28 AM - Sujit Back To Top (10134)
Can you change the name of the Report Snapshot?

Currently it stores the snapshot with the date and time. Can this be changed manually to give it a more meaningful name?

Thanks, Sujit.














get free sql tips
agree to terms