Ssrs linked reports not updating

17-Dec-2015 00:33 by 7 Comments

Ssrs linked reports not updating - dating berkshire

We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources.

In this tip I will go over the steps needed to enable report caching for SQL Server Reporting Services reports.SSRS has a built-in caching capability where the data required to render a report can be retrieved from the Report Server Temp DB database instead of executing queries on the database specified in the report's data source.Caching is enabled in the Execution Properties for the report in the Report Manager.You can expire the cache based on elapsed time or a schedule.When a user runs a report enabled for caching, the Report Server Temp DB database is checked to see if a cached version exists; if it does the data is retrieved from the cache, otherwise the data is retrieved from the data source and cached for future use.Depending on the number of users running reports and how often they run reports, caching can reduce the load on your database servers by not running queries when a cached report is requested.

One point to emphasize about report caching is that the cache expiration is not triggered by changes to the underlying data; it is based on the expiration option you choose, either number of minutes or a schedule.

The tradeoff with caching is that as soon as a new cached version of a report is created, the underlying data could change and the changes will not be reflected in a cached report until the cache expires.

In this tip we will walk through the following steps to show how to enable caching on a report: In order to enable caching on a report, any data source used by the report must store the credentials for connecting to the database in the 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: 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.