ThoughtFactory.CreateThought() A random collection of thoughts from an easily bored developer

Refreshing Reports using URL access in Reporting Services

In the past I've been a fairly big advocate of the Microsoft Report Viewer control for accessing SQL Server Reporting Services reports from within a web application, simply because it allows authentication to be proxied through the host application (so you can use a service account to talk to reporting services and control security through your application rather than adding every user in your application to reporting services), but the control requires a traditional ASP.Net webforms Form and page life cycle, so it's out of the picture for ASP.Net MVC development.

To solve the problem I turned to Reporting Services' URL Access functionality that allows direct linking to reports while still giving you control over parameters, the toolbars and other functionality through the use of variables passed in via the query string. I used iframes to get the inline reports onto the page, and just linked directly to the reports that were meant to open in their own window.

This was great until I noticed that the data on the report was not being refreshed at a regular interval, leaving users with stale data on screen. A quick check of the report in Report Manager confirmed that the data was in the system and the report updated correctly when shown through Reporting Services' own interface.

A quick inspection of the execution parameters of my report (located under properties->execution for each report in Report Manager) showed that I was not using the in built Reporting Services caching, so I figured I was dealing with some sort of HTTP caching. This lead me to try one of the oldest tricks in the "how to fool the HTTP cache duration" book, random junk parameters. The theory behind this is that adding a random parameter that is not used by an application to the end of a query string will tell the web server that this is a request that it hasn't seen before, and hence it will need to figure out the actual result rather than looking in the cache. I postfixed the URL with something along the lines of "&RefreshParameter=" + System.DateTime.Now.Ticks.ToString(), and hoped for the best.

Unfortunately it did nothing. Realising that the problem had nothing to do with HTTP caching I thought that I'd try and apply the same trick to an actual report parameter to see if reporting services could be fooled in a similar manner. The URL Access feature allows you to specify reporting services parameters on the query string in the exact same format as the "RefreshParameter" I had already created, so I simply had to make a new parameter on each of my reports called "RefreshParameter" that accepts blank/null values and does nothing. This worked a treat, enabling me to have current data displayed in iframes on my MVC site.

Update: Peter Van Ooijen suggests that using the rc:ClearSession=true parameter may work in some scenarios (but apparently not all). Some people seem to have had good mileage by combining both the session clearing and the random variables.