SQL Server Reporting Services Hits its Stride

We’ve all heard that version 3 of a Microsoft product is when that product really hits its stride. And while I’m not sure of the truth of that software urban legend, I do have to say that Microsoft, with the release of SQL Server 2008 Reporting Services (which happens to be its 3rd major version), has released a gem of a reporting solution that is sure to please users of prior versions and bring more converts into the Reporting Services fold. The newest version of SQL Server Reporting Services is faster, can handle larger reports, supports a wider variety of browsers, and is more flexible thanks to the new Tablix data region, support for rich text content, and Microsoft Word as an output format.

One of the biggest, yet mostly invisible, changes Microsoft has made to SQL Server Reporting Services (SSRS) for SQL Server 2008 is that it has decided to ditch Internet Information Services (IIS). SSRS now runs directly on top of HTTP.SYS server API, completely bypassing IIS. This means that SSRS no longer has to worry about other IIS applications impacting reporting performance. And by making this change, as well as the move to an on-demand report processing engine, Microsoft made significant speed and scalability improvements to the SSRS engine. SSRS can now execute much larger reports and execute them faster as well. For installations where SSRS must coexist on a server alongside of IIS, SSRS and IIS do not conflict. However, when run under Windows XP alongside of IIS, reporting services cannot be hosted on port 80. (The suggested port 8080 works just fine, though.)

Microsoft made virtually no changes to security in SSRS: NTLM, Kerberos, Negotiate, Basic, and Custom protocols are supported as before. If you need to make changes to authentication, for example, to allow basic authentication (which is disabled, by default), you need to modify the RSReportServer.config file rather than use the IIS MMC snap-in tool since SSRS no longer runs on IIS.

I was dismayed to find that Reporting Services still doesn’t support a form-based security model out of the box. Ideally, I’d like to see a future version of SSRS offer a mixed security model, much like SQL Server’s database engine offers. It’s worth noting, however, that such a model is supported if you configure Reporting Services to work in SharePoint Integrated mode, but that’s a big and potentially expensive commitment that many installations (including the one I manage for my organization) are not willing to make.

Nothing much has changed in terms of the SSRS Web Service API. In fact, the namespace is still RS2005 (RS2006 if you are using Reporting Services in SharePoint Integrated mode). Microsoft retired the RS2000 namespace, however, so if you still have apps using that older endpoint and you wish to take advantage of SSRS 2008, you will need to rework your code.

Report Rendering

One of my favorite improvements to SSRS is hardly mentioned by Microsoft but I know it will be very exciting for organizations like mine that need to support multiple browsers and platforms: the HTML rendered by Reporting Services renders nicely in most major browsers, including Internet Explorer, Firefox, Chrome, Opera, and Safari.

Speaking of rendering, Microsoft made a number of other improvements in this area too, including improved output to Excel and Text formats. For Excel, SSRS now supports subreports and nested data regions. For CSV (text), you can now opt for Excel or CSV-compliant modes. However, the big story here is the newfound support for the Microsoft Word format. SSRS can now render any report to the Word .DOC (not .DOCX) format, which is supported by Microsoft Word versions 2000-2007. See Figure 1 for an example of a report rendered to Word.

One thing that hasn’t changed much with SSRS 2008 is Report Manager. This very capable browser-based tool for managing your Report Server reports is much the same as before. Its sibling, however, the SQL Server Management Studio-based Report Manager that was introduced with SSRS 2005, has been discontinued, which is no big deal since hardly anybody bothered to use it.

Report Designer Improvements

The Visual Studio-hosted report designer has gotten a significant makeover in this version. Prior versions of Reporting Services had three views: Data, Layout, and Preview. SSRS 2008, however, has moved data manipulation to the Report Data pane and replaced the three views with two: Design and Preview. You can see the new designer in Figure 2. Notice the Report Data pane on the left and the grouping pane at the bottom center of the design surface.

The best thing about the Report Data pane is that report parameters and built-in fields such as page number are now surfaced right there above the dataset fields as you can see in Figure 2. On the negative side, I find Reporting Services 2008’s insistence that I create a named data source just to point to an existing data source a real unnecessary annoyance.

Microsoft has improved many of the dialogs in SSRS 2008. One of the best examples is the Report Properties dialog, which finally allows you to directly select page orientation rather than force you to swap the height and width values when switching from portrait to landscape.

The New Tablix Data Region

Microsoft has replaced the separate table, matrix, and list data regions with a single unified region: Tablix. But if you look for Tablix on the Report Items tab of the Visual Studio 2008 Toolbox you’ll be surprised to find it’s not there. Rather, you will still find separate items for table, matrix, and list. In reality, Tablix is there behind all of those regions but Microsoft thought it would be better to give you starter “views” of the Tablix region rather than have a single Tablix region with a very complicated user interface.

So why did Microsoft introduce Tablix? One word: flexibility. Previously, when you crafted a report you were forced to choose a tabular, matrix, or list report. With SSRS 2008, you do the same, but the difference is that you can take a tabular report and add a column (dynamic crosstab-style) group. Or perhaps, start with a matrix report and add a static column to the left or right of a column group. The possibilities are endless.

For example, let’s say you start with a matrix report as shown in Figure 3. You could have created this report, rptSalesByYear, in SSRS 2005. It has two row groups: Country and City and one column (dynamic) group Year and displays the sum of sales in each cell of the matrix. But let’s say you wanted to display to the right of the year columns the salesperson assigned to that city. This would be impossible in SSRS 2005 but in Reporting Services 2008, it’s a snap. From Design view, right-click on the Year column selector and select Insert Column|Outside Group-Right from the popup menu as shown in Figure 4. Next, drag the SalesPerson field from the Report Data pane to the new empty column to the right of the Sum(Cost) column and you have now created a hybrid data region that has attributes of matrix and tabular reports as shown in Figure 5. Tablix reports also support displaying more than one column group in a report and a number of other variations of the Matrix, Table, and List data regions.

In theory, the Tablix offers you infinite flexibility and this is, of course, good. In practice, however, there are a few kinks. Doing simple things, for example, like making the table header repeat at the top of every page of a tabular report, is too complicated. I needed to read a blog post just to figure out how to accomplish this. The solution is to click on the little arrow to the right of “Column Groups” in the grouping pane (look at the bottom right of Figure 2) and select Advanced Mode. This changes the grouping pane to show normally hidden static groups. Select the top group under Row groups and then use the Property window to set its RepeatOnNewPage property to True. Wow, that’s a lot of non-obvious steps for a fairly commonly needed feature.

By: Paul Litwin

Paul Litwin is Programmer Manager at Fred Hutchinson Cancer Research Center in Seattle, leading a group of programmers in providing custom application development for cancer and AIDS research. Paul is also the conference chair for Microsoft ASP.NET Connections, president of Deep Training, a Microsoft ASP.NET MVP, a member of the INETA Speaker’s Bureau, an ASP.NET Insider, and, over the years, has managed to write a few books and training classes. Currently, Paul is authoring a course on SQL Server 2008 Reporting Services. http://paullitwin.com