Typically, most of the reports in SSRS are executed on demand. It means when a user requests a report – first, the query in the Dataset will run, and then the respective report will be displayed. This entire process within the Report manager is called the Processing Options. It might be an ideal approach for small data sets. But what about large Datasets or long-running queries? In this situation, you can use the Report Snapshot in SSRS.
A snapshot in SSRS is a pre-executed report which contains the layout information and the Dataset used for the report. You can use this snapshot option to store the report data based on the schedules. So, you can restrict the user from accessing the long-running queries.
This article will show you the steps involved in creating a snapshot in SSRS with an example.
Before we start creating a Snapshot in SSRS, Let me open my Report Manager to show the existing reports on our server. For this SSRS Snapshot demonstration, we will use the previously created report called Insert Page Breaks in Report. So please refer to the same to understand the SSRS Report data.
Let me show you the Report Preview.
Create Report Snapshot in SSRS
To create an SSRS report snapshot on Page break, Click the down arrow beside the report to open the menu items. Please select the Manage option from the menu items.
Once you select the Manage option, the Report Manager navigates you to a new page with the following tabs. Use this page to manage the advanced setting of this report. Under the properties tab shows the Report Created, user, created and modified dates, and the Report size.
There are two types of snapshots in SQL reporting services:
- Report Execution Snapshot: You can create this type of SSRS Snapshot using the processing Options tab. This type of Snapshot is useful to reduce the query processing time by allowing the query execution at a specified time (scheduled time).
- Report History Snapshot: You can create this type of Snapshot using the Snapshot Options tab. This type of Snapshot will store a copy of the chart in the Report History tab at a specified time (scheduled time). Please refer Report History Snapshot article to understand the configuration settings.
In this article, we will explain the creation of Report Execution Snapshots in SSRS. So, Let me show you the default settings of the processing option by selecting the Processing Options tab.
- Use the following schedules to create report snapshots: This option allows you to configure the schedule options — the report query will process at this scheduled time.
- Create a report snapshot when you click the Apply button on this page: As its name suggests, it will create a snapshot once you click the Apply button at the bottom of this page.
Let me show you the default settings of the SSRS snapshots options page. We will explain to them in our next article.
From the above screenshot, you can see that it is showing an error message: Credentials used to run this report are not stored. Let me solve that error.
First, navigate to the Shared Data Source that you are using for this report, and then select the Manage option.
NOTE: The following steps are mandatory for creating Snapshots, Cached Reports, or Report Subscriptions.
Once you select the Manage option, the following page will display. As you see from the properties tab, this data Source uses Windows Integrated Security, which is unacceptable for snapshots.
- Credentials stored securely in the report server: You must specify the username and password to log into the data source. These credentials will store inside the report.
- Windows Integrated Security: It will pass the current user’s Windows credentials to an external server to connect with the data source.
So, please change the option to Credentials stored securely in the report server, and provide the credentials required to log in to the SQL server.
Next, go to the Processing options of the Page Breaks report, and select Render this report from a report snapshot. It means when the user calls for a report, it will load from the snapshot.
Let me schedule the time for query processing. To do so, click the Configure button.
Once you click on the Configure button, the report manager will navigate you to a new page. You can use this page to schedule the SSRS snapshot.
- Schedule Details: Use this section to schedule the report snapshot Hourly, Daily, Weekly, Monthly, or Once in a lifetime. Don’t forget to mention the start time.
- Start Date: Please specify when you want to start the Report Snapshot.
- Stop this Schedule on: When you want to stop this Snapshot.
For the SSRS Snapshot demonstration purpose, let me schedule for 2 minutes. In real-time, it might be hourly or Daily.
Click Apply to close the SSRS snapshot configuration.
As you can see, it has nothing to do with report history.
Let me run the Page Breaks report. No matter how often you run within two minutes, the report will render from the snapshot (not from the real dataset).
Whenever you schedule something in Report Manager, a new Job will create automatically at the Server level. This Job executes automatically as per what we planned. To see the schedule options, Right-click on the Job and select the properties option.
Click on the properties option in the context menu opens the following window. Please navigate to the Schedules tab to check the Job schedules.
As you can see, it shows the schedule properties that we defined in the Report Manager.
Let me show you the log file of the Job. As you can see, it executes every two minutes.