Larry Steinle

Reporting Services provides software developers with a powerful, easy to use tool to construct reports for the web. Reports can be easily integrated inside applications using Reporting Services Url Access Parameters. In today’s post I’ll review the two url access parameter syntaxes explaining the differences between them.

Default Reporting Services URL Syntax

The default url syntax runs the report inside the Reporting Services Interface web pages. The default syntax displays breadcrumbs allowing the user to navigate thru the various report folders in the site to find reports that they can use. Of course each report and folder can (and should) be locked down to specific user groups to ensure the security of the data.

http://ReportServer/Reports/Pages/Report.aspx?ItemPath=Folder/Folder/Report&ViewMode=Detail

In this syntax report caching is controlled in the report parameters. By default report caching is disabled.

While this url syntax works generally we want the report to appear as if it is part of our own site. This syntax makes it obvious that the report is part of another web site and can lead the user to other reports when we want to control which reports the user see’s to begin with.

URL Access Syntax

URL Access Syntax allows the programmer to hide the breadcrumb and standard Reporting Services menus from the user. All the user see’s is the report window controls which allows the user to modify report arguments and the report itself. Nothing else. As a result when the report is hosted within an IFRAME it is drawn on the screen in such manner that the user is led to believe they haven’t left the host website.

http://ReportServer/ReportServer?Folder/Folder/ReportName&rs:Command=Render&rs:Format=[HTML / Excel / PDF / Word / etc]

For a full list of options refer to the MSDN Using URL Access Parameters documentation guide.

This is also the most flexible option. In addition to viewing the report as HTML, the HTML can be bypassed and the report immediately rendered as PDF, Word, Excel or any other supported format.

There is one unexpected behavior to the URL Access Syntax. Caching is enabled by default. When caching is enabled the report stores a copy of the data for each set of parameter arguments. So if the report is called again with the same arguments the cached copy is returned instead.

While caching makes the system more efficient if you aren’t aware of the behavior it can be very frustrating. Your trying to run the report but it appears to be failing. Are you connected to the correct data source? Yes. Is the Url pointing to the correct report? Yes. Are the arguments correct? Yes. Is the query on the database working correctly? Yes.

These questions are asked only in the event that you realize the data should have changed. Some systems the data is static for a long enough period of time that you might test the report and think it is returning current data when it is actually returning cached data.

The first time I encountered this problem I found another individual who had the exact same symptoms as myself. In Garry’s article, Refreshing Reports using URL access in Reporting Services, Garry described creating a dummy parameter and passing in a date/time stamp to ensure that the report query could not be cached. While Garry provide a creative workaround I didn’t care for all the queries that would be cached on the server using up memory and valuable resources.

After a bit more research I discovered an MSDN article with the answer! In the MSDN Article, Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services, Mr. Hancock revealed the ClearSession query string parameter. The ClearSession parameter forces the report server to ignore parameter arguments and disables the caching behavior with a simple, supported, declarative url syntax.

http://ReportServer/ReportServer?Folder/Folder/ReportName&rs:Command=Render&rs:ClearSession=TRUE&rs:Format=[HTML / Excel / PDF / Word / etc]

Unless you actually need the report to be cached remember to add the rs:ClearSession parameter to the url.