Aside

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.

About Garry

I'm a Solution Architect living in Perth, Western Australia. I work primarily with Microsoft technologies, but I have an open mind and like to branch out into other areas in my spare time. I'm also a Microsoft Virtual Technology Solutions Professional (VTSP) in the Integration space and like to stay active in the local development community.

,

8 Responses to Refreshing Reports using URL access in Reporting Services

  1. Daniel O July 9, 2009 at 12:18 am #

    One limitation we found from using the URL to pass in parameters is if you are passing in a large section of text (say over 1500 characters) you start to hit the limit of what can but on on a URL.

  2. Garry July 9, 2009 at 12:39 am #

    These are fairly simple reports. In fact, I’m using the logged in user to drive most of them so the only real parameter is my random parameter.

    Out of interest, why are you passing in over 1500 characters worth of parameters? That sounds like a lot, even for a complex report.

    It would also be interesting to see if you can do a POST (and therefore get around the character limit). Not exactly RESTful, but it might get you out of a jam.

  3. Daniel O July 9, 2009 at 3:05 am #

    Yes, we ended up implementing a form POST to address the limit. The reports being generated were letters, so the users would type in several paragraphs of text.

  4. Garry July 9, 2009 at 3:39 am #

    Wouldn’t it have made more sense to have a form that stores the stuff in an intermediate table and then just give it the ID of the letter you want to display in the report?

  5. Daniel O July 10, 2009 at 6:09 pm #

    True, but then you have to commit it to provide preview access and clean it up if the user cancels out of the letter generation wizard or closes the window.

    The real solution would have been if the customer let us have SQL 2008 RS, then we’d just render it to word with the basic details from the DB, and then the user edits in the body of the letter directly in word.

  6. Garry July 10, 2009 at 8:35 pm #

    You could have also made a simple form which, on submission, calls the Reporting Services web service using your form variables as parameters, then use your app to display the returning HTML 4.01 (or PDF or RTF or whatever you want). It’s a SOAP payload so you can send up whatever you want (well, within the web server’s set limits, of course).

  7. Thomas Trung Vo August 22, 2011 at 10:31 pm #

    Make Report by Reporting Service with SharePoint List http://sharepointtaskmaster.blogspot.com/2011/08/make-report-by-reporting-service-with.html

  8. self defense April 27, 2014 at 11:23 am #

    Thank you, I’ve recently been searching for info approximately this topic for a long time and yours is the best I have found out so far.
    However, what concerning the conclusion? Are you certain concerning the
    supply?

Leave a Reply