SQLServerCentral Article

Reporting Services!



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


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


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...



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating