Featured Database Articles

A Brief Introduction to Reporting Services

Reporting Services is a comprehensive reporting solution
that integrates numerous components within an elastic, web-based architecture.
These components include:

The tool set necessary to create, manage, view and deliver
reports throughout the organization;

Open application programming interfaces (APIs) for the
integration of reports, or an overall business intelligence solution, in a wide
range of diverse business environments;

A server-based, highly scalable engine for processing and hosting
reports.

When we adopt the MSSQL Server 2000 / Reporting Services
combination, regardless of the extent of the evolution of our BI efforts
before, we avail ourselves of a highly integrated, powerful aggregation of BI
components that includes:

The MSSQL Server RDBMS, within which we can house organizational data for analysis;

The Data Transformation Services tools with which we can perform flexible, yet
powerful, extraction, transformation and loading ("ETL") of data
between operational data stores and the BI warehouses and marts;

Data-mining capabilities for finding patterns and correlations
in large amounts of data;

Other support and development tools that allow us to
rapidly develop and deploy BI applications within our organizations.

Although its own central
reporting database is housed within MSSQL Server, Reporting Services can easily
access data from a wide range of data sources via ODBC and OLE DE. Reporting
Services was designed with seamless integration in mind, and it will become
known as a universal solution among a diverse range of environments. While
Reporting Services also integrates with a wide variety of report creation tools
and environments, it contains everything administrators and authors need to
create and deliver reports without the requirement to write the code involved.
The development environment is contained within a graphical tool called Report
Designer, which works inside the project
framework of the feature-rich Visual Studio .NET development environment. Illustration
1 depicts a report that is under development
in the Report Designer.

Illustration 1: A Report Under Construction in Report
Designer

Report Designer plays an important role in the
upsizing of MS Access reports into Reporting Services. We use Report
Designer to import MS Access reports, either from an MS
Access database (.mdb) or MS Access project (.adp)
file. Each imported report is converted to Report Definition Language ("RDL")
by the Report Designer. RDL is an XML-based standard for report
definition, developed by Microsoft and several industry partners.

All reports created in Reporting Services are defined in the
RDL format, which generates XML in a way that is transparent to the
report author. The RDL standard is being adopted rapidly by ISV's from
the perspective of support, so upsizing reports from MS Access, just like
writing them "from scratch" in the Report Designer (or a
custom authoring tool, if appropriate) only means more integration and enhanced
usability of our information products.

We will be examining the import / conversion process in our
practice exercise later. Let's take a look at some of the realities of
conversion that we will need to understand before undertaking the conversion of
our MS Access reports to Reporting Services.

The Realities of Conversion

The Basics

As we said, we can import reports from an .mdb
or .adp file. In either case, the full set of reports associated with
the file will be imported, and converted to .rdl files. In our practice
example, we will import a full set of reports from a copy of the sample Northwind
database. Other ways to manage this might be to create a "halfway
house" .mdb, through which we can pass the reports at a controlled
pace. We can also import the entire set of reports into a surrogate report
project that we create beforehand in Report Designer, then, once the
importation / conversion to .rdl files has occurred for the full set,
open the individual reports in another report project, for a degree of
insulation or staging, if that is useful for some reason. In any case, the
original reports, and the MS Access database that houses them, are safe from
corruption or otherwise disrupted utility.

Keep in mind that Access 2002 and above
are supported as import targets of Reporting Services. Access 2002 or later is
the only native import option, with regard to having a menu item
reserved for that purpose, but the import and conversion of other popular
report formats will soon become common options. I have already worked with
multiple organizations that are in the process of creating such capabilities
for Crystal Reports (several parallel ventures are underway in this arena,
apparently), as well as for specific products of Cognos, BO and other "Big
Sister" applications. Utilities of this sort will become commonplace as
organizations all over the planet begin to see the huge savings involved in
making the switch - and especially see others doing so around them. Once this
train gets rolling ... well, we all know the metaphor.

In addition to
being housed within an Access 2002 or later database, Reporting Services will
require that the data sources associated with any reports designated for
import must be available for the conversions to be successful.
Reporting Services, as we shall see, actually converts the data source
to a Reporting Services source that is active as soon as the report is
converted and available.

Some MS Access Components are Not Supported

As we might expect, some modules, controls
and other components found in import-targeted MS Access reports are not
compatible with Reporting Services. The resulting build errors are presented
in the Task List of the Design Environment (normally the lower window),
where we can review what was culled out of a given report to ascertain whether
its rejection was crippling to the report in its new incarnation in Reporting
Services. Because the two reporting environments are significantly different,
Reporting Services may modify some of the features of the imported reports, or
may reject the components entirely.

Supported features are detailed in the
Reporting Services Books Online. In addition, an overview is
provided that treats the manner in which MS Access features and components are
converted to RDL. The documentation explains, in many cases, what the
action taken by Reporting Services can be expected to be, when it
encounters an unacceptable item, and whether a warning or error is likely in
the Task List. The components that are discussed include:

Data Sources

Report Modules

Report Controls

Report Properties

Grouping Properties

Expressions

Functions, including

Array

Conversion

Database

Date/Time

DDE/OLE

Domain Aggregate

Error Handling

Financial

Interaction

Inspection

Math

Message

Program Flow

SQL Aggregate

Text

Constants

Parameters

Object Names

Rectangles and Containment

Bitmaps

Other considerations are discussed here and elsewhere in the
Books Online, as well, including the fact that conditional
formatting is not automatically converted, that the report properties
description field in MS Access is not converted, and so forth. This
section of the Books Online is a "must read" for any
practitioner who seeks to prepare for the sometimes tricky process of importing
his or her organization's reports into Reporting Services. (For that matter, as
of this writing the initial offerings on the book market for SQL Server
Reporting Services is, in my opinion, so poor that the Books Online
- which they essentially recast on paper - is still the best source of
information that exists in a single document. In this case, I feel that the
independent reviews we see on the major online booksellers are mostly quite
accurate.)

Despite the differences in the two
applications, and the features that may exist in our MS Access reports that
will not carry over to identical functionality in Reporting Services, we can still
often obtain efficiencies by using the import feature to upsize our reports.
This is particularly true when our reports are not heavily endowed with the
features or components that do not readily translate, or if the number of
non-convertible items is minimal. If we have resources on hand that make the "import
and modify" process more efficient than fresh rewrites, then the import
capability obviously still offers a boost in the upsizing effort. It is,
however, important to consider that the reports that we translate via this means
may still warrant examination by knowledgeable practitioners to determine if
enhancements or additions may be available in the far more powerful Reporting
Services environment, and if the upsizing process might present an opportunity
to make our reports more useful and performance oriented, as well as more
easily deployed, delivered, and so forth via the new Reporting Services
paradigm.

Let's begin our practice exercise at this
point, preparing a database with reports to convert, and then getting hands-on
exposure to the process in general. This will provide some insight into the
decision to perform this on a larger scale later in our respective
organizations.