Visual Studio 2012 and SQL Server 2012 Reporting Services—Still Out of Sync

I'm a big fan of SQL Server Reporting Services (SSRS)—and one of its most vocal critics. I want SSRS as well as Visual Studio to be all they can be, so with each new version, my hopes climb in anticipation.

We all had great expectations for the 2012 release of Visual Studio and SQL Server. The Microsoft teams know about the issues that the MVPs and I raised last year (and for the past 10 years). However, few of the improvements we asked for were implemented. When we ripped open SQL Server 2012's fancy paper, it seems all we got is a repackaging of SQL Server 2008 R2—at least as far as the reporting technologies are concerned. Little was done to bring SSRS in sync with Visual Studio. To better understand the issues, let's back out the screws and take a look at the inner workings of SSRS, then look at what was and wasn't addressed in SSRS 2012 and Visual Studio 2012.

SSRS: The Sequel

SSRS is built around a pair of report processors that consume the XML-based Report Definition Language (RDL) report code and extrude the report. One report processor is on the client, and the other is hosted on the SSRS server.

On the client, you basically have a report processor and development tools, such as SQL Server Data Tools or Business Intelligence Development Studio (BIDS), Report Builder, and the ReportViewer control. The client-side development tools execute the client-side report processor to interpret the RDL code and render a WYSIWYG version of the report as it would be rendered by the server using its own matching report processor.

The server-side implementation of SSRS provides a considerable amount of additional functionality, including security functionality, data source management, scheduling, report snapshots, and data-set caching. The report processor sits at the core of the SSRS architecture.

Over the past decade, the report processors have changed several times. The last update was with SQL Server 2008 R2, when the RDL version was bumped to 2010. That's when maps and many other cool features were added.

Keep in mind that the report processors are version-dependent. That is, they only know how to interpret RDL for a specific version of SSRS. As shown in Figure 1, an RDL report's Report element (coded in XML) includes the version, such as 2010, 2008, 2005, or 2003.

Figure 1: The Report Element That Identifies the RDL Version 

The new tools released with SQL Server 2012 can create 2010 or 2008 RDL but not older versions. This means nothing has really been changed in SQL Server 2012 as far as features that report developers can access are concerned. As far as what the SSRS engine can do, there are changes.

What's New in the SSRS Engine

There are a number of powerful new features in the SSRS engine. My favorite new feature is data alerts, which is an alerting solution that lets you know about changes in report data. Another noteworthy new feature is Power View, which can be used to develop reports. It's meant to co-exist with Report Builder, which hasn't changed. (That's too bad, because Report Builder really needed some stability work.) You can read about these two features in MSDN's Data Alerts (SSRS) and Power View (SSRS) web pages.

The Big But: SharePoint Dependency

Data alerts and Power View are useful features, but there's an anchor holding them back: SharePoint dependency. Their SharePoint dependency makes these new features out of reach for many companies. The reality that Microsoft doesn't accept is that most companies don't use SharePoint and have no plans to do so. Although the SharePoint adoption rate might increase a bit because SharePoint integration has been re-architected as a SharePoint 2010 shared service, there's not really an incentive to convert if you've invested a couple of years in SSRS and have built up a catalog of reports, rights, subscriptions, and users. There's still no wizard or utility to convert your catalog over to SharePoint. This means you have to start from scratch—without SSRS's Report Manager.

SSRS Development Tools Evolve

The SSRS development tools used by most serious developers have been an add-in to the Visual Studio shell and not Report Builder. Until recently, these tools were called the Business Intelligence Development Studio (BIDS). In SQL Server 2012, BIDS was replaced with the SQL Server Data Tools (SSDT).

There are a few more details in the Data Developer Center, but essentially SSDT incorporates the tools that developers need to manage database schema and source control. This is a good start. However, at the 2012 MVP Global Summit, we discussed ensuring that changes made to the schema are tracked back to the applications that consume them. For example, if you change the data type of an input parameter on a stored procedure, wouldn't it be nice to know what applications are dependent on (and will break) when this object is changed? This suggestion didn't make the cut.

The Visual Studio Shell Game

Skipping over the early history, in Visual Studio 2010, there was no BIDS support at all. Although the ReportViewer control was supported, it worked only with outdated 2008 RDL. For two years, developers had to use the existing SQL Server–included BIDS tools built with the 2008 version of the Visual Studio shell. Thankfully, the BIDS shell was dual-headed. It could easily switch between 2008 and 2010 RDL—a brilliant move because the 2010 version supports maps and a lot of powerful report elements. However, the 2012 ReportViewer control application couldn't access the 2010 reports in local mode. Its report processor could only interpret 2008 RDL. In Visual Studio 2012, it's the same—the report processor is still designed to interpret only 2008 RDL. (In case you're unfamiliar with local and server modes, the local mode in the ReportViewer control uses the client-side report processor. Server mode simply treats the ReportViewer control as a smart browser window to display a server-generated report—just like a browser-invoked report.)

What's in a shell? In other words, who cares? Well, if you create mildly sophisticated reports, you should. That's because you probably include Visual Basic code in your report expressions. Some of this code might be as simple as an IIF statement to implement green-bar to highlight adjacent lines in a report or as complex as a formula to plot a missile trajectory or a quarterly profit-and-loss statement that even the IRS will accept. Ideally, this Visual Basic code should be tested before you paste it into the report. As it is, there's no mechanism to independently test Visual Basic code in an SSDT or BIDS project. There isn't even a syntax checker. This means you must have two separate toolsets installed—Visual Studio and SSDT (if you're running SQL Server 2012) or BIDS (if you're running SQL Server 2008 R2, SQL Server 2008, or SQL Server 2005).

Sorting Out What Works

When I go over this in my webinars, I can almost see the students' eyes roll back in their heads trying to figure out what works and what doesn't. To help them understand, I provide the diagram shown in Figure 2. As a rule of thumb, the ReportViewer control's local (client-side) report processor supports only the one-off version of RDL.

Figure 2: RDL Support by Version 

To make everyone more frustrated, Visual Studio's support for SSRS projects doesn't typically ship with the release to manufacturing (RTM) bits (if it does at all). I expect to see this update sometime in the future. Until then, you'll have to get used to the new SSDT. Will the new Visual Studio 2012 ReportViewer control ever support RDL 2010? I have no idea, but I certainly hope so.