The Skinny on SQL Reporting Services

As I mentioned in a previous post, we have standardized on SQL Reporting Services for our reporting needs. Coming from a Business Intelligence consulting background I had used a handful of powerful reporting solutions such as BusinessObjects, Spotfire, etc, so I was very leery when I first started experimenting with SQL Reporting Services.

After working with it for over two years, I can safely say that it is a very decent (and free) reporting solution for people running SQL Server. For small to medium businesses who already own SQL, odds are with a little bit of experimentation, you can make reporting services work for you.

I keep observing more implementations with Reporting Services and even see it rolled out in some packaged applications as the reporting platform. We just upgraded to the latest Great Plains build and they now have SQL Reporting Services canned reports you can deploy.

I got a call from one of the co-founders of the DotNetNuke platform today and they want to use a SQL Reporting Services Module I wrote to run their marketplace reporting. I can attest from the traffic and interest in this piece of software alone that Reporting Services deployments are on an upward trend.

So if you asked me for a brain dump what SQL RS does well and what it can be better at, here’s the skinny.

The Pros of SQL RS

Powered by web services & XML - The reports are published as Report Definition Language (RDL) files which are nothing more than XML documents describing the report. You deploy these to a reporting services web services application. Once there, you can use the canned web interface to access reports or you can integrate the reports into your applications or interface leveraging a control and the web service. This gives the ultimate flexibility for how you want to present the reports contained in the reporting services repository. The diagram here gives a great visual on the reporting services architecture.

Scheduling - Reporting Services includes a great scheduling mechanism for being able to schedule reports to be emailed or snapshot to a file share on a set schedule. This is great for reports which you need to be distributed to a group of people on a timed basis.

Exporting Capabilities - Perhaps one of the most used features of Reporting Services is the ability to export a report to a variety of formats. You can export them as HTML, TIFF, PDF and even as Microsoft Excel. This is great if a user wants to take a dataset returned in a report and do additional analysis, they can simply dump it to excel and go to town.

Drilling & Linking - Reporting Services makes it easy to link reports together. Say you have a grid of customers and the number of loads you hauled for that customer in a month and you had another report that showed the detail loads for a customer, you could link the customer ID in the summary report so that when clicked it drills to the detail report magically passing in the customer. This allows seamless and intuitive navigation throughout the reports.

Multiple Datasets & Sources - The ability to bring multiple queries into a single report so that a single report can contain data from a variety of sources is great. The data source doesn’t even have to be SQL Server!

Web Based - The canned interface is all web based which allows you to publish reports to the web available for access everywhere. If you have a desktop application you want to leverage the reports in, you can leverage the web service API to make that work as well.

Free (sort of) - Can’t say enough about this one, if you are licensed on SQL 2000 or 2005, you already own this solution.

Evolving - Do a Google sometime of SQL Reporting Services, it’s all over the place today and growing and evolving rapidly. There are a ton of new features in the 2000 to 2005 build and the 2008 build is looking to follow right on that trend. MS has put together a great starter kit which contains sample reports and good examples to get you started.

Ad-Hoc - I put this here and on the flipside. In SQL RS 2005 they added the ability to put a layer of abstraction on top of a data source which allows end-users to drag and drop objects to build reports on the fly using a web deployed tool called Report Builder. I was very excited to see this feature released since ad-hoc analysis is typically a huge requirement for most reporting solutions. You can read on to see why it’s also stated on the flipside.

Canned Functions - There are a plethora of functions which you can leverage to format and manipulate data contained in a report. Need something uppercase and trimmed and to do some math, no problem.

Extensible - Need to execute some custom function to manipulate or format data? Not a problem, you can extend a report by putting .NET code logic in the report to do just that.

The Flipside

Lacks Advanced Features - Reporting Services is great for probably 90% of our reporting needs. It does fall down in a few areas such as the graphing capabilities. They exist, but if you wanted to generate more complex graphs that contain trend lines, multiple axis, etc., you will be hard pressed to do it.

Ad-Hoc - As mentioned, it’s on the flipside simply because it is a typical version 1.0 implementation. It is great users can now build their own reports on the fly having the details for how this is done on the backend hidden from them, but it is weak. They can only do the basics and the layout of the reports are pretty canned and not open to much customization. This is probably good when users are just looking to query to get answers, but it does leave a lot to be desired by some power users.

Visual Studio Report Creation - The main report designer is integrated in to Visual Studio. This is great in that if you are a .NET developer, you’re intimately familiar with it, but if you are not, writing reports means you have somewhat of a learning curve since VS can be overwhelming.

Interactivity - While it has gotten better in the latest release of SQL RS, the ability for users to start marking a report up has been crippled. While they allow some sorts on the fly, doing things like re-arranging columns, conditional formatting, hiding data and other on the fly viewing functions are not available unless you open the report in the report builder or dump the data to excel and go from there.

Loops - A traditional tricky condition that exists in non star schema designed data structures is the fact that loops exist within data. That is you can take more than a single path to join tables together which offer different answers depending on which path you take. How you handle loops is traditionally not of much concern until you try to enable ad-hoc analysis. I’ve not seen an easy to use method for handling this in RS.

The bottom line is SQL RS is a rock solid and evolving solution. I don’t regret one second our decision two years ago to standardize on it. If you have SQL Server today and haven’t investigated it yet, I’d recommend checking it out. This article doesn’t even talk about the sister solution of SQL Analysis Services…more to come on that when I get around to it.

Popularity: 90% [?]

Bookmark To: