Cached Reports in SSRS

A Cached Reports in SSRS is a saved copy of an already processed report. If your report is generated with huge amount of data, or it is designed from large dataset then it will be time-consuming for the report server to call the same report again and again. In these situations you can create a cached report. And by this we can save the report rendering time.

Before we start creating cached reports in SSRS, Let me open my Report Server to show the existing reports in our server.

Cached Reports in SSRS example

In this article we will show you the steps involved in creating cached reports in SSRS with example. For this demonstration we are going to use the previously created report called Create Bar Chart in SSRS Report Builder so please refer the same to understand the Report data.

In order to create a cached report on bar chart, Click the down arrow beside the Bar Chart 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 this report (advanced operations, I must say). Under the properties tab, it will show the Report Created, modified dates, and the Report size.

You can create a Cached Reports in SSRS in multiple ways:

• Using the processing Options
• Cache refresh Options

Cache refresh Options

Please navigate yourself to Cache Refresh Options tab, and select the New Cache refresh Plan option as we shown below.

The following pop up message will be displayed. Click OK button

Once you click OK it will ask you the description of the Cache report, and the cache refresh time.

We assigned the cached bar Chart as the description, and clicked 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 cache.

• Schedule Details: Use this section to schedule the cached report 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 caching.
• Stop this Schedule on: When you want to stop this cache.

For the demonstration purpose, let me schedule for 1 minute. In real-time it might be hourly, or Daily etc.

Click OK to close the cache configuration.

From the below screenshot you can see that, we successfully created a New Cache refresh Plan to cache SSRS report.

Let me edit the bar chart using the report builder by selecting the Edit in Report Builder option

Let me run the report. As you can see from the below screenshot we had a Execution Time, and when ever we refresh (or re run) the report the time will be refreshed

Now let me run the report from the Report Manager by clicking the Bar Chart

Let me refresh the report.

As you can see from the above screenshot, the execution time is not changing because it is displaying the Cached report. If you try after one minute then the Execution time will be updated because we defined the cache time as 1 minute.

Cached reports in SSRS using Processing options

Let me show you the default settings of the processing option by selecting the PIE Chart because we already changed the cache option of the bar chart.

• Do not Cache temporary copies of this report: By default, Report server will assign this option. It means, report will always call the dataset, render, and then display report with live data.
• Cache a temporary Copy of the report. Expire copy of report after a number of minutes: This option will cache the report by given number of minutes.
• Cache a temporary Copy of the report. Expire copy of report on the following schedules: This option allows you to configure the schedule options. we already explained these options in the previous section.

Now let me open the Bar Chart Processing Option. As you can see from the below screenshot it is using the second cache option. This option is automatically selected by the report server, based on the Cache refresh options that we specified.

Let me open the SQL Server management Studio to see, what is really happening in SQL Server. When you create a cache report in the report manager, a new Job will be 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.

Thank You for Visiting Our Blog