Overview: An introduction to using snapshots in SQL Server Reporting Services, including an explanation of the differences between a “Report Execution Snapshot” and a “Report History Snapshot.”
Intro to the Ways Users Can Run Reports in Reporting Services
1. On Demand. Most reports typically are executed on demand – i.e., the query in the dataset runs and the report is displayed at the time the report is requested by the user. This is the default in “Processing Options” within Report Manager (accessed via the “Manage” menu option).
2. Cached Reports. If you have a long-running query that cannot be tuned further, one option you might look into is caching. With this method, the first user that executes the report will wait for the report to render. Subsequent users, until the cache expires in minutes or on a schedule, will view the cached report. Getting the benefit of caching depends on the subsequent users having the same security and same parameter choices that the first user had.
3. Snapshots. If you have a long-running query, or perhaps a dataset you don’t want users accessing at any & all times of the day, then a snapshot might be a great option. A snapshot can be thought of as a pre-executed report, usually run on a schedule. The rest of this blog entry focuses on using snapshots.
Prerequisites for Using a Snapshot in Reporting Services
There’s a couple of requirements in order to use a snapshot. If you are familiar with subscriptions in SSRS, these will sound familiar.
a. Stored credentials for the data source. Windows authentication is not acceptable when a report is scheduled. If you aren’t using a data source with the ID and Password stored (so that data level security is a non-issue when it runs), you’ll get the message “Credentials used to run this report are not stored.”
Tip: Create a separate shared data source that has a name such as “MCGardenCenterDW_StoredCreds” so you know at a glance it’s using a specific ID and Password as opposed to Windows Authentication (assuming Windows auth is your default). You may even put these any data sources with stored credentials into their own folder with limited permissions.
b. Defaults for all Parameters. Another prerequisite for using snapshots is that all parameters need a default value defined. This makes sense as there’s no user interaction when a snapshot is being populated at, for instance, 4am.
Tip: Sometimes you might have to be a bit clever in order to ensure all parameters have a default. For instance, if the default is always “current fiscal period” that continually changes, you can make this happen with an intermediary hidden parameter – it might just take a bit of extra time to think up a good solution.
Scheduling a Snapshot
As you are perusing the options when you “Manage” a report, you will notice there’s two different schedules associated to snapshots. First, there’s the scheduling options on the “Processing Options” page:
Secondly, there’s scheduling options on the “Snapshot Options” page:
Initially you might say “huh?” or wonder which should be set. The key here is that there’s really two kinds of snapshots…
Two Different Types of Snapshots in Reporting Services
On the Processing Options page, you would schedule a “Report Execution” snapshot. The primary purpose for a Report Execution snapshot is usually to improve performance by reducing report rendering time, or to ensure queries are passed to the source database at very specific times.
Conversely, on the Snapshot Options page, you are setting up a “Report History” snapshot. The primary purpose for a Report History snapshot is to keep a copy of the report at a specific point in time.
So, basically you want to make sure you set the schedule associated to the purpose you’re trying to accomplish. Usually it’s just one or the other, but it could be both depending on the requirements. Microsoft explains it like this:
“Snapshots that are generated as a result of report execution settings have the same characteristics as report history snapshots. The difference is that there is only one report execution snapshot and potentially many report history snapshots. Report history snapshots are accessed from the History page of the report, which stores many instances of a report as it existed at different points in time. In contrast, users access report execution snapshots from folders the same way that they access live reports. In the case of report execution snapshots, no visual cue exists to indicate to users that the report is a snapshot.”
Avoiding the “Selected Report is Not Ready for Viewing” Error
Let’s say on the Processing Options page you checked the radio button to “render this report from a report snapshot.” Then you scheduled it on the Snapshot Options page. Sounds reasonable, right? You can even view the report within the Report History just fine. However, you click on the report name (i.e., the normal way to run a report) you get a message: The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available.”
Why the message? And where’s the report? What happened is with the setup just described, a Report History snapshot now exists but no Report Execution snapshots exists. So, SSRS has no report to render given this circumstance. To resolve, it does depend on what you are trying to accomplish, but you probably want to schedule it on the Processing Options page instead. (Alternatively, if the data rarely changes you could control when it’s created by using the Apply button option. The Apply button is also useful for generating the first snapshot for testing.)
Viewing Report Execution Snapshots within Report History
There’s one more really important thing to be aware of. Let’s say you schedule your Report Execution snapshot within the Processing Options. The report runs fine, but you don’t see it within Report History.
By default the option to “Store all report snapshots in history” is not checked. If you want to see a Report Execution snapshot within history, you’ll want to check the box to do so within Snapshot Options. If you’re only using Report Execution snapshots for this report, you’ll probably want to store them in the history. However, if you happen to be using both Report Execution and Report History snapshots for the same report, you might want to leave it unchecked so they don’t get mixed up – i.e., if you are doing both, then the schedules will differ for a reason.
Letting the User Know When the Data was Refreshed
Since a snapshot will render data at a particular point in time, it’s very kind to the end users of the report to display the “data as of” data in the report header (or footer, however you have it standardized). To ensure it displays when the snapshot was created, you’ll want to use the ExecutionTime global field (as opposed to Now() or Today() type of functions). For example:
=FormatDateTime(Globals!ExecutionTime, DateFormat.ShortDate)
Finding More Information
MSDN – Set Report Processing Properties
MSDN – Processing Options Properties Page (Report Manager)