SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Reporting Services!

By Mike Pearson,


When word of SQL Server Reporting Services (SQLRS) first got out - it caused a huge amount of 'movement' in the reporting industry.  I have seen reactions to it that range from borderline hysterical to "ho-hum version 1 stuff". So just how good is it really, and once all the marketing hype has been pushed aside, just how good is it really?

Down to Basics -  What Is SQLRS

The role of SQLRS is to provide a mechanism for report authoring, management, and delivery.  It is scalable enough to be used in small business right through to large enterprises, and installs onto any Windows-based server that will take .NET Framework 1.1. For the techies, the minimum installation requirements are:

  • Web server with ASP.NET and Internet Information Services (IIS) 5.0 or higher. MDAC 2.6 or higher.
  • Connection to SQL Server 2000 SP3a instance.
  • Default website accessible through http://<servername>. (so if you are installing it on a server with WSS/Sharepoint or CMS hogging the default site there are few few tricks to follow)
  • For Windows 2003, the computer must be configured as an application server.

Is it Worth All The Hype?

In a word, "yes". 

Now let me be careful here, lest I sound like a hypnotised junkie chanting "m-i-c-r-o-s-o-f-t,  m-i-c-r-o-s-o-f-t...".   Working at one of Australia's foremost BI Consultancies, means it's crucial that I keep as objective a view on these kind of things as possible, otherwise the advice I offer clients is not "best practice" - is it?.   So we have invested an awful lot of time in doing the research as to how far we can push SQLRS, and to try and define exactly what role SQLRS should play in an organisation.  This article then, is a brief look at our experiences with SQLRS.

We started off months ago by developing SQLRS solutions alongside the traditional Crystal solutions to get an idea of functionality, ease of use, feature-sets and so on.  Now I don't want to go revealing ALL our trade secrets :) - but SQLRS stacked up very well.  So below are some the reasons why I stand by my assertion the SQLRS is going to change the way we view reporting.

Report Authoring

The report designer interface is integrated into Visual Studio 2003 IDE (see below) so it is already a familiar environment for most developers. We found this meant that the usual "ramping-up" time that developers need with new products was hugely reduced, as well as giving all the advantages of a managed code environment. 

Creating reports is very 'drag-n-drop' - which is I have no doubt is the result of one of the design goals of Whidbey - "developers must write 70% less code".  Looking at is simplistically, creating reports is a case of

  1. Define the data sources
  2. Drag-n-drop-n-format the required objects for the report
  3. Publish the report.

Okay, there is a bit more to it if parameters, sub-reports, etc are used, but I think you get the point I'm trying to make about following simple steps.

This meant that in the case of existing reports (where the data sets were already defined in stored procs, queries etc), we could convert reports to RDL (Report Definition Language, which is an XML-based industry standard used to define reports) very easily.  The time varied between minutes to several hours depending on the complexity of the reports. No doubt there will be a large scope for conversion tools in the future as the RDL standard gets more widely adopted.  To provide some idea of skill levels required, if you can create reports in MS-Access, you'll find SQLRS very easy.

Speaking of MS-Access, SQLRS supports a wide range of common data sources.  Basically if you can create an OLE DB or ODBC connection to your data-source then you are home dry.  We have also written a wrapper to create reports from data sourced from existing web-services, so don't under-estimate the flexibility of what SQLRS will bring into the development environment.

Report Management

Once the reports have been written, the report definitions, folders, and resources are published and managed as a Web service. These managed reports can be either be viewed on demand; or published on a specified schedule.  These published reports can be distributed to subscribers as they require. More of this under 'Report Delivery'.

The management interface for the report service is very comprehensive, and allows some very flexible security models to be applied.  We are implementing SQLRS in organisations which up until now have had nightmares with their reporting strategies because of the complexity of their "who-can-see-who-can't-see" policies.

Reports are cached for consistency and performance, and historical snapshots can also be configured if required.

Report Delivery

SQL Server Reporting Services supports both on-demand (pull) and event-based (push) delivery of reports.

On-demand reports are typically first viewed in HTML format, but if required, nothing stops the developer calling a report from a current application and getting the page in a variety of formats such as HTML, PDF, TIFF etc.  As long as you pass a valid URL to the web-service you'll get your report.

Users who wish to subscribe to 'pushed' reports can have those reports delivered in the various formats supported, either via email or delivered directly to a specified file share. SQLRS typically requires a schedule to do this, but we have also been able to write a component that will generate and push a report once certain data-driven criteria (rather than a time-based schedule) have been met.  So again, don't under-estimate the flexibility of what SQLRS offers.

The Cons

Let's be fair.  It is a first generation version of the product, so there are a few holes to be aware of...

Sometimes, using parameters can be a bit of a fiddle. We've yet to be beaten, and have been able to solve 'funnies' when they've cropped up, but a few of the resolutions have left us thinking "What the...?".

SQLRS does support OLAP Cubes, but only through the use of static MDX statements, so if you do want to use it on top of cubes you will have a bit of work to do there.  To fully understand the reason for this, it must be remembered that SQLRS was written primarily for SQL Yukon, which handles cubes quite differently.  Enough said.


Given that SQLRS effectively comes as a FREE add-in for SQL Server (much like SQL Notification Services did), you get an unbelievably powerful tool for your efforts.  Is it the perfect, all encompassing reporting solution?  No, it has holes and is still first generation.  But every client we have introduced it to so far has had the same approach towards it's adoption : it's powerful, it's flexible, ...and it's basically free, so we'll happily find a way to deal with the holes...


Total article views: 10501 | Views in the last 30 days: 1
Related Articles

SQL Server Reporting Services R2 - Publishing Report Parts

SQL Server Reporting Services R2 – Publishing Report Parts SQL Server Reporting Services R2 (SSRS...


Reporting services

Reporting services


SQL Server 2008 Reporting Services

SSRS 2008 Report Service on SQL Server 20005 Database


Report Builder or Reporting Services?

Should you be using Report Builder 3.0 or SQL Server Reporting Services 2008 R2 to build reports? H...