Typically, most of the reports in SSRS are executed on demand. It means, when a user request 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 as the Processing Options. It might be an ideal approach for the small data sets. But what about the large Data sets, or a long running queries?. In this situation you can use the Report Snapshot in SSRS.
Snapshot in SSRS is a pre-executed report which contains the layout information, and the dataset that is used for the report. You can use this snapshot option to store the report data based on the schedules. So that you can restrict the user from accessing the long running queries. In this article we will show you the steps involved in creating a snapshot in SQL reporting services with example.
Before we start creating Snapshot in SSRS, Let me open my Report Manager to show the existing reports in our server. For this demonstration we are going to use the previously created report called Insert Page Breaks in SSRS Report so please refer the same to understand the Report data.
Let me show you the Report Preview.
Create Report Snapshot in SSRS
In order to create a report snapshot on Page break report, 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, Report manager will navigate you to new page with following tabs. You can use this page to manage the advanced setting of this report. Under the properties tab, it will show 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 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 a report in 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 about 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. Report query will be processing at this scheduled time.
- Create a report snapshot when you click the Apply button on this page: As its name suggest, 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 snapshots options page. We will explain 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 as we shown below.
NOTE: Following steps are mandatory for creating Snapshots, Cached Reports, or Report Subscriptions. So, please pay your attention.
Once you select the Manage option, following page will be display. As you can see from the properties tab, this data Source is using the Windows Integrated Security which is not acceptable for snapshots.
- Credentials stored securely in the report server: Here, you have to specify the user name and password to log into data source. These credentials will be stored inside the report.
- Windows Integrated Security: It will pass the current user windows credentials to 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 login to SQL server.
Next, go to the Processing options of the Page Breaks report, and select the 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, report manager will navigate you to new page. You can use this page to schedule the snapshot.
- Schedule Details: Use this section to schedule the report snapshot on Hourly, Daily, Weekly, Monthly, or Once in a life time. 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 demonstration purpose, let me schedule for 2 minute. In real-time it might be hourly, or Daily etc.
Click Apply to close the snapshot configuration.
As you can see, it has nothing to do with report history.
Let me run the Page Breaks report. No matter, how many times you run within the two minutes, report will render from the snapshot (not from real dataset).
Whenever you schedule something in Report Manger, a new Job will be automatically created at the Server level. This job will automatically executed as per we scheduled. In order to see the schedule options, Right-click on the Job and select the properties option from the context menu.
Once you click on the properties option in the context menu, following window will be opened. Please navigate to Schedules tab to check the Job schedules.
As you can see it is showing 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 is executing every two minutes.
Thank You for Visiting Our Blog