Sql – SSRS 2008 – caching data used by multiple reports

reporting-servicessql server

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.

Best Answer

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".

Related Solutions

How to get Visual Studio 2008 to edit SSRS 2005 Report Projects w/o Installing SQL Business Intelligence Development Studio 2005

You cannot. Check this forum posting which has a reponse from Microsoft.

Yes, it was an active decision that the 2008 design evironments would not support continuous backwards compatability but would rather be a one way upgrade. This was not a casual decision and it is understood how it can be an impact.

You can read the entire posting for more details on why you are unable to use VS2008 for SSRS 2005 projects. Very annoying, but I guess I can understand why they had to make that decision. Just about the only reason I have VS2005 on my dev machine now is for Reporting Services projects since our company is not going to be upgrading to SQL Server 2008 anytime soon.

Sql – Enable SSRS My Reports using Express edition

This is, honestly, a guess as I don't have SQL 2008 Express to test this on - but it definitely works on SQL 2005 standard.

I think you can configure the property using rs.exe

Create a text file (I'm assuming you'll call it myReportsEnable.rss) and add the following text to it:

Public Overridable Sub Main()
    SetSystemProperties
End Sub

Private Sub SetSystemProperties()

    Dim Properties(0) As Microsoft.SqlServer.ReportingServices2008.[Property]
    Properties(0) = New Microsoft.SqlServer.ReportingServices2008.[Property]
    Properties(0).Name = "EnableMyReports"
    Properties(0).Value = "True"
    RS.SetSystemProperties(Properties)
End Sub

(I'm getting this from SQL2005, so I'm assuming that Microsoft.SqlServer.ReportingServices2008 is the right type to use - apologies if it's not)

Save the file, then execute it from a command line using rs.exe:

rs -i myReportsEnable.rss -s http://~YourServerName~/reportserver

Best Answer

Related Solutions

How to get Visual Studio 2008 to edit SSRS 2005 Report Projects w/o Installing SQL Business Intelligence Development Studio 2005

Sql – Enable SSRS My Reports using Express edition

Related Topic