Introduction
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
- Define the data sources
- Drag-n-drop-n-format the required objects for the report
- 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.
Conclusions
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...