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.