SQL Server 2008 R2: Reporting Services – As You Like It

March 3, 2010

In SQL  Server 2008, Reporting Services got quite an overhaul with many features that I really like. In SQL Server 2008 R2, there are even more improvements in  Reporting Services that I am anxious to put into production. There is one new feature in particular that I,  like many report developers, am eagerly anticipating – the RenderFormat global variable. You can use it in an conditional expression to change  a report property value based on the current rendering format. In other words, you can build one report and change the behavior by rendering format – as you like it!

Before I explain how to use it, let me explain a common problem that it solves for me. Many of my Reporting Services projects have its most commonly used rendering format. One project might be primarily intended for online viewing, so the design work focuses on making the report attractive, interactive, and sized for the user’s standard screen. However, when the user wants to export the report to Excel, features that render nicely online do not render so nicely in Excel. Sometimes headers cause merged cells to appear in the Excel format, which then affects your ability to sort or filter by columns. To work around this issue, I design a second report to get a layout that works better in Excel. That is, I’ll create a minimal version of headers that doesn’t cause merged cells and create a simple table layout that renders nicely in Excel, but looks rather plain in HTML format. I then add a link to the HTML version to make it easy to get to the Excel version.

From a technical perspective, I can use this technique in Reporting Services to produce one report that displays online in HTML and another one that winds up as a table in Excel. From a practical perspective, though, I have two reports to maintain. And it’s potentially confusing to users to have to remember whether to use the Export to Excel command or click a link to get to the Excel version. The situation compounds further when I need to anticipate the export of the report to yet another format, such as PDF. Yikes!

That’s where the RenderFormat global variable in SQL Server 2008 R2 Reporting Services comes to the rescue. On report headers, I can set the Hidden property using a conditional expression that checks the value of RenderFormat. Maybe I have two different styles of headers on the same report, HeaderA and HeaderB. For online viewing, I can set the Hidden property to False on HeaderA to display it when the report opens in any format (other than Excel) and then if the user exports to Excel, I can switch the Hidden property to True. Likewise, HeaderB’s Hidden property would be set to True when the report is in any format (other than Excel) and False when the report is in Excel format.

Here is the expression for HeaderA’s Hidden property:

=iif(Globals!RenderFormat.Name="EXCEL", True, False)

And here is the expression for Header B:

=iif(Globals!RenderFormat.Name="EXCEL", False, True)

Note that EXCEL must be all caps here to work properly. Actually, it’s not so much that the name of the render format needs to be in caps, but it needs to match the render format’s name as it appears in the rsReportServer.config file (in Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer). Look for the <Render> element. Here’s an example of the default render formats available in SQL Server 2008 R2 Reporting Services:

XML
CSV
ATOM
PDF
RGDI
HTML4.0
MHTML
EXCEL
RPL
IMAGE
WORD
There are many other features I’ll be discussing in future posts, but I picked this one as my favorite new feature because it’s the very one I need on a project right now!

3 Responses to “SQL Server 2008 R2: Reporting Services – As You Like It”

  1. I have a report that when rendered to excel the COLUMN Header doesn’t repeat to its succeeding pages.. Will the RenderFormat in this case useful? Do you know a work around?

    I tried changing the properties of static columns and tried also adding the in RS Config.. But all of these didn’t work for me.

    True

    Your help will be appreciated.

  2. Sorry – I don’t know of a work around. You might try posting to the Microsoft Reporting Services forum (http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads/) to see if someone there has an idea.

  3. You have to go to advanced mode on the row/columns section at the bottom of the designing page with the little switch on the right of this section.
    Then click on the row (named static) you want to repeate and set RepeatOnNewPage on the properties window to True.
    At this point you still don’t get repeated columns.
    You now set KeepWithGroup on After and then the columns of the row will repeat.

Leave a Reply