About the Series …
This article continues the series, MS Access for the
Business Environment. The primary focus of this series is an
examination of business uses for the MS Access relational database management
system. The series is designed to provide guidance in the practical application
of data and database concepts to meet specific needs in the business world. The
majority of the procedures I demonstrate in the series will be undertaken
within MS Access 2003, although the concepts that we explore in this
article will apply to MS Access 2002, and beyond.
For more information on the series, as well as the hardware
/ software requirements to prepare for the tutorials we will undertake, please
see the first article of our series, Create
a Calculated Field with the Expression Builder. Along with
MS Access, of which we have made repeated use in the previous articles of the
series, additional application considerations apply for this article, because
it introduces another Microsoft application, MSSQL Server 2000 Reporting
Services ("Reporting Services").
For those joining the series at this point because of a
desire to work with Reporting Services and its components from the
perspective of converting / importing MS Access Reports to Reporting Services,
it is assumed that, along with Reporting Services, MSSQL Server 2000, Visual
Studio.NET and any other appropriate support applications are accessible to /
installed on your PC, with the appropriate access rights to the associated environments
to parallel the steps of the article. If this is the first time Reporting
Services or any of these other components are being accessed on your machine, you
may need to consult the Reporting Services installation instructions, ReadMe
files, and the associated online documentation for installation and
configuration instructions. In addition, my Reporting
Services series at Database
Journal gives substantial guidance in various aspects of Reporting Services
setup that may be of assistance.
Introduction
Many businesses that use MS Access databases eventually push
to the limits the multi-user applications they have purchased or developed
internally, and find the "upsize" path to MSSQL Server to be
attractive. Common scenarios that have driven this sort of upgrade include the
desire to avoid disconnecting data from Web servers
for the "repair and compact" evolution that becomes familiar to those
supporting heavily used MS Access databases. Other scenarios driving upgrades
are the sizes that MS Access databases reach, as well as the need to
incorporate features and efficiencies that only a larger RDBMS can provide.
While the objectives and rewards
of moving the database components to MSSQL Server are relatively well known, up
until now the upsizing path for MS Access reports would not have been so
readily obvious. We could possibly "leave them where they are," as
it were, in a local MS Access database linked, client-server fashion, to the
new upsized database (with the performance considerations and feature
stagnation that would accompany such an approach), rely upon third-party
reporting tools, or perhaps seek other approaches. However, overall, no
straightforward reporting upgrade solution offered itself to the upsizing user
– that is, until the advent of MSSQL Server 2000 Reporting Services.
In recent weeks, clients and
non-clients alike have contacted me for strategy discussions surrounding
conversions of existing analysis and reporting solutions to the new Reporting
Services application, which I have been working with since beta. Many readers
of my Reporting Services series have contacted
me to request information along these lines, as well. A great deal of interest
has been specifically shown in the area of converting MS Access reports, with
which we have worked at various times throughout this series. Most of these
have been from practitioners considering an upsize to MSSQL Server of their MS
Access applications / components, or from organizations that have already
completed such an upsize and are coming to terms with the need to enhance the
performance of existing reports – avoiding the added, typically excessive, cost
of third-party solutions where possible. The appearance of Reporting Services
as an MSSQL Server 2000 add-on not only answers the need for enhancing existing
MS Access reports (while often allowing us to avoid a total rewrite), but
offers us opportunities to inject OLAP components, supported by the power of
MSAS, into our reporting structures, as well as myriad other capabilities that
the reporting capabilities in MS Access were simply never intended to provide.
Reporting Services provides a
platform from which we can manage the complete reporting life cycle–from a
report’s design, to its deployment in a managed-server environment, to delivery
to the end user. Reporting Services allows us to avoid the time consuming and
otherwise costly creation of dynamic web pages and other delivery mechanisms.
Moreover, as anyone in a large organization who has priced any of the dominant,
third-party enterprise reporting solutions can attest, Reporting Services offers
an opportunity to shave a six-to-seven-figure line item off the analysis and
reporting budget, because any organization with an MSSQL Server 2000 license
can centrally develop and maintain the entire analysis and reporting function,
as well as deliver reports to information consumers in a wide variety of
formats. Information products can be easily delivered through flexible,
scheduled ("push") methods or via consumer requests ("pull")
scenarios.
I could go on about the powerful
options within Reporting Services for quite some time, but that is really part
of the purpose of my RS
Series, and beyond the scope of this article. Suffice it to say that,
after years as a BI and data architect, I consider myself an evangelist for
this new paradigm in reporting. Reporting Services promises to commoditize
business intelligence for the benefit of every business, large and small,
making sophisticated analysis tools available to any organization licensing
MSSQL Server 2000 and beyond.
In this article, we will examine the conversion capabilities
built into Reporting Services, in anticipation of the need to upsize MS Access
reports. We will then prepare a database from a commonly available sample for a
hands-on practice example of the import process. Our examination of the
conversion process for MS Access reports will include the following:
-
An introduction to Reporting Services, focusing upon the Report
Designer development environment; -
A discussion of some of the realities of conversion, including:
- Ways that Reporting Services differs from MS Access
- Features of MS Access that readily convert;
-
Features of MS Access that require at least some intervention as
part of conversion.
-
Preparation of an MS Access database (with reports) upon which to
base the practice exercise; -
A hands-on, practice exercise that includes the simultaneous
conversion of several MS Access reports to the Reporting Services environment; -
Verification of the effectiveness of the conversion process via
an examination of the newly converted reports.
