MS Access for the Business Environment: “Upsize” MS Access Reports to MS Reporting Services

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.
William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles