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(RenderFormat.Name="EXCEL", True, False)

And here is the expression for Header B:

=iif(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:

I recently performed an installation of Reporting Services 2008 R2 in SharePoint Integrated mode on two servers. The SSRS integrated mode R2 version is supposed to have much improved performance over the previous version. I’m still in progress of benchmarking tests, so can’t comment yet, but there were some installation steps that I had to perform above and beyond the documented steps that I thought I’d post here as a reminder to myself – and anyone else that has a need. These steps were tested against the November CTP of R2 and might change when the Release Candidate comes out soon. I’ll post more complete instructions at that time and point out what changes, if anything.

It’s amazing how natural it feels to write out 2010 on checks this first week of the new year. Usually it takes me at least a month. Maybe that’s a sign of good things to come this year. Probably like many of you, I look forward to the clean slate that the start of a new year brings, although intellectually I know it’s an arbitrary marker in time. Nonetheless, I am excited by the new year and the plans that are underway. One of my resolutions is to blog more. With the upcoming release of SQL Server 2008 R2, there will be plenty to blog about. However, I’m currently heads down on writing a course and co-authoring two books that deal with the BI features in R2, so I won’t start blogging in earnest until I can get past my current set of deliverables. Which will be soon. Very soon. Consider this a teaser!

TechNet is publishing a series of four articles introducing readers to business intelligence. I have written two of these articles while a friend of mine, Derek Comingore of BI Voyage, has written one of them. The fourth article is not yet written, but I’ll post a link here when it becomes available. A lot of people refrain from using the BI components in Microsoft SQL Server because they don’t want to undertake building a data warehouse. There are lots of good reasons to build a data warehouse, but if you are the only consumer of data, you obviously don’t need a data warehouse, but I think you ought to be able to use these tools anyway to help you do your job better. If only you knew how. This article series should help get you started.

Life as an independent consultant is interesting and challenging, but it gets a little lonely in the sense that you don’t spend a lot of time with your industry peers. That’s why I enjoy getting out to conferences each year. I always run into people I know and admire and get to pick their brains on some nagging issue I’m battling at the time and I always get to meet new people who are doing interesting things. And of course I get to share what I know, too. This year I’m going to the Best Practices Conference for the first time, but I recognize most of the speakers’ names so there will be many familiar faces and I feel privileged to be included with them.

I’m presenting the following two topics –

BI316, Optimizing Your MDX Queries

Retrieving data from an Analysis Services cube is supposed to be fast, right? Then why are your queries running so slowly? Many factors can contribute to poor query performance. If you have applied best practices design principles to cube and dimension design, the next step is to evaluate the design of your MDX queries. In this session, you first learn how to know whether your query or the cube needs to be optimized. Then you learn the principles of optimal MDX query design in Analysis Services 2005 and Analysis Services 2008, and how to modify your queries to deliver data faster.

BI337, Designing Analysis Services Cubes for Performance

Building a cube is simple. After all, you have a lot of wizards at your disposal to do the development work. But building a cube that delivers data quickly requires you to make additional changes to the database design. Come to this session to learn what steps you need to take in the development process to ensure the cube is designed for optimal performance, whether you’re using Analysis Services 2005 or Analysis Services 2008. You learn not only how to properly design dimensions, aggregates, and partitions, but why these design principles improve performance.

If you’re planning to be there, be sure to stop by and say hello!