Disabling the SQL Reporting Services cache

Posted by Peter van Ooijen on October 5, 2005

SQL Reporting Services keeps a copy of every report rendered in its cache. Of course this is quite a performance boost when a lot of users request the same report over and over again. But in our case this cache turned against us. We have an ASP.NET app whose reports are included in the app as a bunch of links to the reporting server. The page assembles the URL with the parameters (more on that later) and redirects the user to the report. Every time the user opened a report she was confronted with the previous version rendered. Based on the previous parameters. Clicking the the RS toolbar’s refresh button (NOT the browser’s refresh button) led to the intended report.


You can configure the caching of reports in great detail. It’s under the execution tab in the report manager.


<IMG src="/photos/peter.van.ooijen/images/132788/original.aspx" p

But there is a little quirk in this. Even when you set the report to Render this report with the most recent data, the user is (in our scenario) still confronted with a cached copy. This is either a bug or a misunderstanding between me and RS, I’ve seen others. What helped me here was using the rs:ClearSession parameter. In our app this is now the base URL for a report.


protected string reportServer
{
    get
    {
        return System.Configuration.ConfigurationSettings.AppSettings["ReportServer"] + “{0}&rs:Parameters=false&rs:Command=Render&rs:ClearSession=true“;
    }
}
 

The report name and parameters go into the {0}


To be continued.

This entry was posted in Data. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • mok

    Hi Peter, I know this post is old, but I’m having the same confusion…

    I tried to work out what was really happening by creating a test report that was set to cache for 60mins with the label containing current datetime (this would really = the rended datetime of the cached report)

    using the RS refresh or view report didn’t change this field value so I’m not convinced that the rs refresh button actually works!

    any ideas?

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    hi Mok,

    it’s an old post but my blog is alive :)

    These settings worked in our scenario but we did experience more and even weirder caching issues with RS. In one case we uploaded new report defintions, did see the new reports in the reportmanager, but the clients kept seeing the old version for days and days. Up till now we havn’t found out what or who to blame :/

    RS is quite nice but does have its quirks. In case we find anything usefull I’ll blog.

  • http://www.vsdntips.com David McCarter

    I tried your suggestion, but the report still does not refresh correctly. Any ideas why?

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    David, I’m afraid not. We’re also still seeing some unintended caches. Which show rows which were deleted from the DB days ago! Reproducable, for specific selections in the report. I wish I knew what’s going on.

  • sinfulmonk

    It works!!! Excellent post. Thanks.

  • Bonginkosi Ngubane (GMSI)

    it worked for me… dont know if its gonna work 4ever thou…

    You are The Man..

  • Mathias

    Found another aproach in a Google group: pass an additional parameter in the URL that changes everytime, like a random number or current datetime. Supposedly Reporting Services recognizes that there’s a different parameter (even if the parameter is not actually used in the report) and refreshes the cache every time the report is loaded. Worked for us.

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    Nice ! Good there is another way as my way did not work for evrybody :)

  • Maddog

    Mathias, how did you get around having to add the dummy parameter to every report? When I add a parameter with the current timestamp to my report URL I get back an error message from the RS engine saying an attempt was made to set a report parameter that is not defined in this report. I am not wanting to add a new parm to 300+ reports in our library. Is there a way to force the RS engine to ignore unmatched parms?

  • http://www.nec.com.ar Ivan

    We are using the tip about include:

    &rs:Command=Render
    &rs:ClearSession=true
    &rc:Toolbar=true
    &rcarameters=true
    &rc:emissionTime=

    Were &rc:emissionTime= is a parameter that we add with the time ticks (in JavaScript tt= new Date().getTime(), then our URL is different each time that the user invoke the call to the report:

    http://ReportServerName/ReportServer/Pages/ReportViewer.aspx?/Reporting+Services+Tests%2fMyTestReport&rs:Command=Render&rs:ClearSession=true&rc:Toolbar=true&rcarameters=true&rc:emissionTime=1181917533962

    Using the extra parameter &rc:emissionTime=, and the others that I have mentioned above, our reports are working properly and we have not more rsExecutionNotFound errors.

    Thanks, and regards
    Iván

  • anne

    Where should i put the things below? Im using reporting services in windows application…

    &rs:Command=Render

    &rs:ClearSession=true

    &rc:Toolbar=true

    &rcarameters=true

    &rc:emissionTime=

    thanks…

  • http://England Craig Mpofu

    Hi Bonginkosi
    What are your new contact detail.My email is craigkq@hotmail.co.uk

  • http://www.gaspodethewonderdog.blogspot.com/ David Roys

    Thanks for this post – you really helped me out of a tight spot and fixed my problem. I had a SharePoint site witha URL that was being built to render a report as PDF – but it wasn’t picking up the latest report definition, until I added the &rs:ClearSession=true to my URL. Thanks Heaps!

  • Christen

    Thanks a million

  • Mike

    I was having report caching problems and cured them by updating the properties of the Reports directory in IIS. On the HTTP Headers tab I added a Custom HTTP Header of Cache-Control with a value of no-cache.

  • Jaap

    You’re my hero! Finally got rid of the SSRS 2008 caching…

  • tugba

    thanks a lotttt!!

    it really works..

  • Gaurang

    Hi Mike,
    Adding a Custom HTTP Header of Cache-Control with a value of no-cache, really cured the issue. Thanks a lot.

  • Ankur

    Hello ,

    How to add no-cache parameter to SSRS 2008 ?

  • Pieter

    Solved my problem to..

  • John

    Thank You Thank You Thank You! This was driving me crazy!!

  • harish

    this works like a charm. thanks

  • Sam

    Awesome, thanks a lot.

  • http://www.visdev.eu Harvey

    Many thanks for this post Peter.
    I built my own report renderer using the reports from SSRS2008 and I tried everything to get the reports to refresh the data properly.

  • Senthilis

    I was strugling with this issue. Made this change and worked perfectly

  • http://www.gmurthy.com Gmurthyusa

    If you are using report viewer web control, then the below code should help

    rptVwr.ProcessingMode = ProcessingMode.Remote;
    rptVwr.ServerReport.ReportServerUrl = “http://yourreportserver/ReportServer”
    rptVwr.ServerReport.ReportPath = “reportfolder/employeereport”

    ReportParameter employeeIdParam = new ReportParameter(“employeeId”, txtEmployeeId.Value);

    rptVwr.ServerReport.SetParameters(new ReportParameter[] { employeeIdParam });

    Context.Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);

    rptVwr.ServerReport.Refresh();

  • Ketan

    I had the same problem and you info cured it.

    Thanks.

  • http://www.facebook.com/AmiTimA Amit Chotiya

    Thanks a lot.

  • Seth

    This was really helpful, thanks!

  • Dejan Dular

    Thaaaaaank youuuuuu!

  • Lee Dalbey

    THANK YOU!!!!   This was just the trick I needed

  • Alex

    you are THE MAN !