Database Introduction to SQL Server 2000 Reporting Services

Introduction to SQL Server 2000 Reporting Services

Lots of people (myself included) are excited about the coming release of
Microsoft SQL Server “Yukon,” the successor to SQL Server 2000. But you don’t
have to wait until 2005 to see one of the advances that “Yukon” is delivering to
developers. Last month Microsoft released SQL Server 2000 Reporting Services, an
add-on for the current version of SQL Server (it will be packaged with new
copies of SQL Server 2000 later this year). In this article, I’ll introduce
Reporting Services and show you how you might make use of it in your own
development efforts.

The Reporting Services Architecture

Reporting Services runs as a middle-tier server, as part of your existing
server architecture. You need to have SQL Server 2000 installed for the
database server, and Internet Information Services 6.0 as a Web server. The
report server engine takes in report definitions, locates the corresponding
data, and produces the reports. You can interact with the engine through the
Web-based Report Manager, which also lets you manage things like refresh
schedules and notifications. End users view the report in a Web browser, and
can export it to PDF, XML, or Excel with the click of a button

Report designers work inside of special Visual Studio .NET projects (so make
sure you count the cost of a VS .NET license when you’re toting up the costs of
moving to Reporting Services). Figure 1 shows the designer, which is pretty straightforward. As you can see, it handles typical “banded” reports perfectly
well, but that just scratches the surface here. The designer can also create
crosstab-like grids called matrixes, graphs and charts, and other interfaces. Of
course you can flexibly group and sort the reports. Finally, because the reports
run in a Web browser, they’re not static. It’s easy to enable drill-down and
drill-through scenarios that allow users to retrieve additional data. Figure 2
shows a Reporting Services report open in the browser.

The designer can also import reports from Access databases, but there are
some limits on this (not all Access features are supported, and you can’t import
just a single report), which limits the usefulness of this feature.

Designing the report is not the end of the process. A report in VS .NET is
only available to the designer. In order to make the report available to
other users, the developer needs to publish it to a report server.

Managing Reporting Services

The Web-based Report Manager lets you perform a wide variety of tasks with
published reports. I suspect this interface will mainly be used by DBAs rather
than developers. For starters, you can change the data source and security
credentials of a report. This is a necessity in any organization that implements
separate development and live servers; changing the credentials lets you deploy
a report from a test server to the real server.

Report Manager also lets you change execution properties for any report. You
may be used to thinking of reports as always being generated from current data,
but that can be a problem if the database is large and a report involves a heavy
load on the database server. Reporting Services offers more tunability in this
area than I’ve seen in other products. You can:

  • Always render the report on demand, from current data (the default
    option)
  • Render the report, and then cache a copy. Serve from the cache for a fixed
    number of minutes.
  • Render the report, and then cache a copy. Serve from the cache on a
    fixed schedule. For example, you can expire the report at midnight each
    weekday.
  • Render the report on a fixed schedule, and then serve that copy until the
    next scheduled render.

You can also set a report timeout, so that long-running reports are killed
before they use up all of your server resources.

Reporting Services features a role-based security scheme that you can control
from Report Manager. You can designate users or groups into default roles of
Browser, Content Manager, My Reports (can handle their own part of Report
Manager, but not other users’ reports), or Publisher, as well as define new
roles and determine which folders and reports these roles apply to.

Finally, there’s a subscription mechanism built right into Reporting
Services. Using Report Manager, you can set up a new subscription to deliver
fresh copies of the report via file share or e-mail. Subscriptions can produce
new copies of the report on a fixed schedule. There are also data-driven
subscriptions, which use information from the report itself to decide who to
send copies to.

Extensibility

Microsoft seems to have given particular attention to extensibility in
Reporting Services. Unlike some competing products, this is a very open
architecture. Here are some of the key points where you can customize Reporting
Services:

  • You can manage a report server via SOAP.
  • You can use .NET to create custom rendering components to drop into any
    report.
  • You can export reports as TIFF, PDF, Excel, XML, HTML with Office Web
    Components, or other useful formats.
  • You can create new data processing extensions to allow Reporting Services to
    draw data from new or unusual sources.
  • You can create delivery extensions to allow subscriptions via media other
    than e-mail and file sharing.
  • You can configure a report server through Windows Management Instrumentation
    (WMI) classes.

Overall, Reporting Services provides an extremely flexible way to make data
available to end-users. The drawback to this, of course, is that learning to
program and extend Reporting Services is not a trivial task. Plan on some
significant developer and DBA investment if you decide to implement this tool in
your own organization.

Pricing and Licensing

Finally, it’s worth taking a look at the licensing scheme for Reporting
Services, and the implications of this for your costs. Microsoft has posted a Licensing FAQ
to help you sort these issues out, but here are the salient points. First, if
you own a SQL Server 2000 license, you’re licensed to run Reporting Services.
That means that if you want to run Reporting Services on the same server as SQL
Server, you’re covered. But remember, this means that you also need to have IIS
6 installed on that server, and the server itself has to be hefty enough to
handle the additional load of constructing and delivering reports, as well as
its current database tasks.

Many organizations will probably find that they need to split Reporting
Services off to its own server. This means buying another server, licensing SQL
Server (the full product; Reporting Services doesn’t work with MSDE), and of
course setting everything up – costs that can easily run into four or five
figures. So keep an eye on memory and CPU utilization, as well as statistics
within SQL Server, as you evaluate this software for actual production use.

A Solid Solution

Despite a few tiny version 1 teething pains (I managed a non-reproducible
crash in Report Manager, and some of the help content is slightly out of synch
with the actual product, SQL Server 2000 Reporting Services offers an impressive
and well-integrated way to create reports from SQL Server. If you’re running a
Microsoft-oriented shop, it’s definitely worth a look. Remember, too, that if
you manage to centralize key reports you may be able to eliminate or
reduce ad-hoc reporting done in Access and Excel, which will have its own
benefits in lowered costs and higher reliability.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his new book From
Coder to Developer
, published by Sybex. When he’s not writing code, Mike
putters in the garden on his farm in eastern
Washington
state.

Latest Posts

Related Stories