SSRS 2008 - caching data used by multiple reports

Question

I am using SQL Server Reporting Services 2008. I have one dataset which is shared among several reports--each report groups or formats data differently. Is it possible to configure things so that the data is pulled from SQL Server when one of these reports is built and then cached within SSRS for a configurable period of time? Assume that I can select against database tables but can not create objects.

Answer 1

Unfortunately no.

Your main option would be to investigate "Report Snapshots" to explicitly cache the report (and indirectly the data)

Otherwise:

• You could change the reports to have a lot of conditional formatting etc so you only have one report. Some caching will happen automatically per user session, but not much

• Use the ReportViewer control in local processing mode, using ASP.NET to cache data

Answer 2

Here's another option, which although complex, might fit your requirements.

Have all of the reports use an XML data source. Then create that XML datasource as an ASP.Net web page or web service. Configure and use SQL Cache Dependency to control when the database would be queried again.

Now, any time any one of the reports asks for the data it will be cached in your "middle tier".

Answer 3

You can now in SSRS 2008 R2:

http://msdn.microsoft.com/en-us/library/ms170438.aspx - Shared DataSets

http://msdn.microsoft.com/en-us/library/ms155927.aspx - Report Caching in Reporting Services

Go SQL!