Unlock the Secrets of SQL Server 2008 Reporting Services

Most of the suggestions I make to the SQL Server, Visual Studio, and VB.NET teams come from readers and the people who talk to me after a workshop session and ask “What were they thinking when they did that?” Thanks to your suggestions, SQL Server Reporting Services 2008 (SSRS 2008) is better. Microsoft kept almost all of the good features and added a few more. That said, I would still like to see some things improved. Let’s visit some of the refined features that should make your job easier and look at a few unfinished features that stick out on the road like a dead moose hit by a cement truck.

Virtual Directory

SSRS no longer needs Microsoft IIS to be able to host virtual directories. Without that added IIS layer, SSRS performs quicker than before. You’ll still need to set up a certificate to be able to access your reports via SSL. Running a production report without SSL is like mentioning your big sister’s phone number on the local country-western radio station. With SSL, all of the chatter between SSRS and the browser travels over the network in encrypted packets. While it won’t make your sister forgive you, she won’t be able to use a net sniffer to see the reports or the private data they contain.

SSRS has also changed how the virtual directories are named, but only if you’ve installed SQL Server 2008 as a named instance (as most of us have). In earlier versions, the ReportServer or Reports URL used the SQL Server instance name tacked on with a $ as in http://[your server]/Report$SS2K5. In SSRS 2008, Microsoft replaced the $ with an underscore (_). (The $ probably offended IIS purists.) To get your Microsoft Visual Studio (VS) 2008 BI project to deploy, you change the TargetServer URL to http://[your server]/ReportServer_SS2K8 where SS2K8 is the name of your SQL Server instance.

Reporting Services Configuration Manager

This tool got a facelift, and its functionality has been polished. Reporting Services Configuration Manager (RSCM) is still launched from the SQL Server Configuration Tools menu. Remember that it’s making configuration changes on a specific SSRS instance, which is often installed on a server down the hall. If you're installing Reporting Services versions later than SQL Server 2000, this tool lets you ensure everything is hooked up correctly and helps you configure SSL, set up email links, and most importantly, set and back up encryption keys. It’s pretty tough to get your reports back without these keys. Figure 1 shows the Report Manager link in the RSCM.

This is where you bind the SSL port to the certificate you created.

Exporting Reports

SSRS still doesn’t offer an easy way to export reports. The Reporting Services team tells me that exporting reports is on their radar (somewhere over the Bering Sea) and the ability to do so might appear in a future release. For now, you can take advantage of a free utility that addresses this need: see Sidebar 1 “Exporting SSRS Reports—For Free.”

Enabling My Reports

If you need to expose Report Manager to your users, you can enable the My Reports feature to help users maintain a directory of reports based on their domain login name. For example, after I enabled My Reports, Reporting Services included a new directory tree for my personal reports that I could use to help find specific reports that interested me. The trick is enabling this feature. The documentation is a bit sketchy, but it’s not that hard to enable once you know the secret handshake.

Start SQL Server Management Studio, but instead of connecting to a Database Engine, choose Reporting Services from the initial Connect to Server dialog box (the service must be running for this to work). To enable the “My Reports” functionality, right-click the base connection and choose Properties. The dialog box shown below in Figure 2 should appear.

You can then set the role applied to each of the My Reports directories created on the server. When Windows users open the Report Manager URL, they will have a new path created in the Reporting Services catalog for their domain name.

It’s vitally important to protect your reports—regardless of who is authorized to see them. The Reporting Services Object Explorer is one way to create specific Reporting Services roles to which you can assign specific rights. This prevents users from accessing reports that they shouldn’t see.

Working with the New Report Designer Actually, many Report Designers are available to Reporting Services developers. In Sidebar 2, “Navigating Microsoft’s Report Designer Maze,” I attempt to lay out in an organized way the versions and what they support.

The new SSRS 2008 Report Definition Language (RDL) rendering engine solves a lot of really ugly problems seen in the first-generation RDL renderers (yes, there are several first-generation renderers). SSRS 2008’s rendering engine supports lots of new Rich Text functionality as well as the new Tablix control (which isn’t really a report control at all). The Tablix control combines the features of the matrix and table controls (thus the name) along with features such as limited support of Rich Text and built-in pivot capabilities.

The new SSRS 2008 rendering engine is also faster and more flexible. That’s good. The problem is that the rendering engine and the Report Designers that build RDL to feed it, created new and incompatible RDL files that can’t be consumed by the old SSRS 2005 engine which is still in use in VS 2005’s and VS 2008’s ReportViewer controls. That’s bad—at least for the people who want to use the easy-to-develop-your-report BI tools to build these new reports and leverage the ReportViewer control in Windows and Active Server Pages (ASP) applications to deploy them. You see, behind the scenes, the VS 2008 SP1 ReportViewer control knows only how to render the old, first-generation RDL–style reports. However, an upgrade to Visual Studio and the ReportViewer control to incorporate the next-generation RDL renderer is underway. Unfortunately, we won’t see the change (despite considerable pressure) until VS 2010.

The SSRS Story Continues

SSRS has many more facets which I hope to lay out for you during this year while Microsoft finishes work on the rest of Reporting Services. I expect Reporting Services to take on a life of its own as more and more architects and developers discover it’s far easier to put together a complex UI that includes parent-child relationships, sums, totals, complex expressions, and embedded code than it is to hard code these elements by hand.