Can the SSRS report cache results

Caching of shared datasets (SSRS)

Query results for a shared dataset can be cached to provide consistent data for multiple reports and to improve dataset query response time. Shared datasets, similar to reports, can be configured to be cached the first time they are used or on a specified schedule.

A shared dataset can appear in multiple reports or be part of a component definition. By caching the shared dataset, you provide consistent data for all reports that use the dataset while reducing the number of dataset queries executed on the external data source.

The following list provides examples of when to cache a shared dataset:

  • The query takes a long time to run.

  • The query uses parameters, but the number of parameter combinations is usually small. Each combination generates cached query results.

  • The query is executed daily, weekly or monthly at fixed times.

  • The query is run in response to a reference to a shared dataset in a report submitted via email, and it is expected that a large number of people will click the link in a short period of time.

The following list provides examples of when a shared dataset should not be cached:

  • The query results must always contain the latest data.

  • The query runs quickly.

  • The query is rarely executed.

  • The query uses parameters, the number of parameter combinations is large, and no combination is more likely than another.

  • Credentials are requested for the data source on which the shared dataset is based or built-in Windows credentials are used.

  • The filter for the shared dataset or query contains an expression with a reference to the global user collection.

If a user selects report parameter values ​​that differ from the default values ​​specified for the cached result set, the dataset query will be actively run and the cached results will not be used for that query.

Caching shared datasets

To enable caching for a shared dataset, you must select the cache option for the shared dataset. After caching is enabled, query results for a shared dataset are cached the first time it is used. If the shared dataset has parameters, each combination of parameters creates a new entry in the cache.

While the query results for a particular combination of parameters are cached, any report that starts processing and that contains a reference to the shared dataset and parameter values ​​will use the cached data.

You can specify how long the data is kept in the cache before it becomes invalid. For more information, see Working with Shared Datasets.

Preloading the cache

You can preload the cache by creating a cache update plan. An update schedule allows you to specify how often the cache should be updated using an item-specific schedule or a shared schedule. To avoid storing multiple cache entries for the same item, the specified schedule should allow enough time for query processing in the external data source. If the query execution z. For example, if it takes 20 minutes, the update schedule should be longer than 20 minutes. See Schedules for more information.

When creating a cache update plan for a shared dataset, the following conditions apply.

  • Caching must be enabled for the shared dataset.

  • The shared data source on which the shared dataset depends cannot be used to request credentials or use built-in Windows credentials.

  • If the shared dataset has parameters, you must provide static default values ​​for any parameters that are not marked as read-only. Read-only parameters always use the default value. To cache a shared dataset for multiple combinations of parameters, you must create a separate cache update plan for each combination of values. Parameters cannot contain references to other datasets.

  • Each cache update plan is associated with only one shared dataset or report.

  • You must have ReadPolicy and UpdatePolicy rights on the shared dataset.

Cache update plans can be applied to shared datasets as well as reports. For more information, see Report Caching (SSRS) Was The Only Way To Preload the Cache.

Conditions that cause the cache to expire

The following conditions can cause a cache for a shared dataset to become invalid.

  • A schedule condition expires. The cache timeout or expiration time is reached.

  • A shared schedule is deleted.

  • Changes to a shared schedule. Shared schedules can be paused, which also affects the expiration time of a cache.

  • The query definition for the shared dataset is changed.

  • The credentials for the shared data source on which the shared dataset depends will be changed.

  • The cache options for the shared dataset are changed.

  • The default values ​​for read-only parameters for the shared dataset will be changed.

  • The filters that are part of the definition for the shared dataset are changed.

  • The shared dataset is deleted from the report server. When a shared dataset is deleted, associated cached copies and cache update plans are also deleted.

Updates to shared dataset cache update schedules do not affect reports that are already being processed. Updates to a cache update schedule only affect reports launched in the future that reference the shared dataset.

additional Information

Manage shared datasets