Upsize your MS Access reports to the new Reporting Services
environment. Bill Pearson discusses the realities in a hands-on walk-through of the process.
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.
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")
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.
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
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;
Server Analysis Services, composed of a robust
OLAP engine that provides a means of developing and creating cubes for highly
effective, interactive 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
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
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:
- Rectangles and Containment
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
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
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
Practice Exercise: Convert an MS Access Report to Reporting Services
As we mentioned earlier, we will create a database
containing reports to illustrate the import process. This will involve copying
the Northwind sample .mdb file that potentially accompanies every
installation of MS Access 2002. If the sample database is not on your hard
drive, whether because you chose not to include it in the MS Access
installation or because it was removed later, either obtain it from the CD or
initialize MS Access, then go to Help -->
Sample Databases, as shown in Illustration 2, and select Northwind
Sample Database from the cascading menu.
If Northwind.mdb is not installed, you will be given
an opportunity to re-create it. If you take this route, close MS Access once
the .mdb is created, remembering where it was placed. By default, it is
typically installed in a location with a path (in an OFFICE10 or OFFICE11
folder) similar to the following:
[DRIVE]: \Program Files\Microsoft Office\OFFICE11\SAMPLES
Once we have located the Northwind.mdb on our hard
drives, we will begin preparation for our practice exercise by taking the
Right-click the Start button on the PC, and select Explore,
as depicted in Illustration 3.
Illustration 3: Open Windows Explorer
Navigate to the Northwind.mdb located above.
Right-click the Northwind.mdb file.
Select Copy from the context menu that appears, as shown in Illustration
Illustration 4: Select Copy from the Context Menu
Navigate to a location in which to store the copy for purposes of
keeping it separate from the original database, as well as locating it easily
through the steps of the exercise.
Right-click inside the folder where you wish to store the copy.
Select Paste from the context menu that appears, as shown in Illustration
Illustration 5: Paste the Copy of the Northwind.mdb
Database in the New Location
The copy of Northwind.mdb appears in its new location
as depicted in Illustration 6.
Illustration 6: The Northwind.mdb Database Copy Appears
Right-click Northwind.mdb once
Select Rename from the context menu that appears, as shown in Illustration
Illustration 7: Select Rename ...
Type OrgReports.mdb as the new name for the database, as depicted
in Illustration 8.
Illustration 8: Renaming the Northwind Database
Click anywhere outside the name to accept the changes.
Let's take a quick look at our new "report inventory,"
to get a feel for what we are about to convert.
the OrgReports.mdb file.
MS Access opens. If you are working with MS Access 2003, as I
am, you may be greeted by the security warning screens. Simply okay the process
of opening the database by clicking Open. (See the Online Help if
there are any issues here). If this is the first time using MS Access, you may
also see an initial dialog displayed, etc. If so, close it; our objective is simply
to open the database to examine reports.
Close the Northwind
splash screen, which appears on launch of the database, by clicking OK.
main switchboard appears as shown in Illustration 9.
Illustration 9: Inside Access, Northwind Main Switchboard
Click the Display
Database Window button, to close the main switchboard.
arrive at the Database Window.
Click the Reports
link in the Objects pane, shown circled in Illustration 10.
Illustration 10: The Reports Link in the Objects Pane
We arrive at the Reports Window, where we can see the
various reports that exist in the database we have copied for our practice
example. The reports appear as depicted in Illustration 11.
Illustration 11: The Database Reports Collection
Open and review
individual reports as desired to get an idea of the various characteristics and
components of each.
exploring the reports contained in the database, we notice some of the reports
have fully convertible features, while others contain components that are not
supported / fully supported in Reporting Services. We will be notified of
unsupported features as part of our upsizing process in the next section.
assume, for purposes of our practice exercise, that we are practitioners in
today's harried business environment, and are faced with providing management
with options for making our reports "better, stronger, and faster,"
preferably, of course, at minimal or no cost. (The budget has been impacted
negatively this year because management has engaged high-priced consultants to
provide options for lowering overall IT costs by off shoring).
currently upsizing our application databases to MSSQL Server 2000, and decide
to "test upsize" the reports to the Reporting Services platform, as
well. We are well aware that the import feature in Reporting Services may not
result in a perfect duplication of all reports, complete with existing
functionality. The way we see it, anything not automatically converted is
probably a good case for examination for enhancement anyway. We can easily
adapt the reports to function within the context of Reporting Services, delivering
rapidly upgraded information products with a little process improvement as a
will convert a complete set of cloned reports, and then investigate any
incompatibilities - managing "by exception" any rework that is
appropriate, and creating a list of the tasks involved as an automatic
byproduct of the process. Sound too good to be true? Let's do it!
we will launch Reporting Services' Report Designer, found in Microsoft
Visual Studio .NET 2003.
the Microsoft Visual Studio .NET 2003 in the Programs group, as
appropriate. The equivalent on my PC appears as shown in Illustration 12.
Illustration 12: Navigate to the IDE in Microsoft Visual
Studio .NET 2003 ...
Once we enter the IDE, we will need to create a
project to house the new imports. Beginning from the Start page, Projects
tab, we will take the following steps:
--> New from the main menu.
from the cascading menu, as shown in Illustration 13.
Illustration 13: Creating a New Project
Project dialog appears. We note the appearance of Business Intelligence
Projects as an option in the Project Types tree, as shown in Illustration
14, indicating an installation of Reporting Services (the folder was
added by the installation of Reporting Services, as it established the Report
Designer in Visual Studio .NET).
Illustration 14: The New Projects Dialog, with Business
Intelligence Projects as a Project Type
Click Business Intelligence Projects in the Project Types list, if necessary.
Project in the Templates list.
Navigate to a
location in which to place the Report Project files.
following into the Name box, leaving other settings at default:
The New Project
dialog appears, with our additions, as shown in Illustration 15.
Illustration 15: The Completed New Projects Dialog
new project appears in the Solution Explorer (upper right corner
of the Visual Studio .NET interface), as we see in Illustration 16.
Illustration 16: The New Project Appears in the Solution
have now created a Report Project, and are ready to import the
reports in the MS Access database that we have prepared, converting each to a Reporting
Services .rdl file in the process.
the Reports folder in Solutions Explorer. The Reports
folder, together with the Shared Data Sources folder, was automatically
put in place when we created the new project in our last steps.
Reports from the context menu that appears.
Access ... from the cascading menu, as shown in Illustration 17.
Illustration 17: Select Import Reports ---> Microsoft Access
The Open dialog appears.
the location where you placed the OrgReports.mdb database file we
prepared for this exercise.
The Open dialog appears, with OrgReports.mdb, as
depicted in Illustration 18.
Illustration 18: The Open Dialog, Showing OrgReports.mdb
within the dialog.
Click the Open
button at the bottom of the Open dialog.
Access window opens (MS Access 2003 users will likely see the warning dialog
appear - if so, simply click Open), and the reports begin processing
immediately. We see the reports appear in the Reports folder of the Solution
Explorer, as shown in Illustration 19.
Illustration 19: The Imported MS Access Reports in the
NOTE: As we can see, the new reports are placed
into the folder where we request their import via right-click. The new reports
become .rdl files, with no obvious means of distinguishing them from
reports that may already exist in our Reports folder, if we are
importing them into an existing Project. This is one reason to set up an
"intermediate" Project to contain the reports, as we have done
in this article, at least until they can be tested, etc., and are ready for "admittance
to the general population" of reports. (I once called this folder "Ellis
Island," for obvious reasons ...).
Even if we are using an existing Project, it is often
a good idea to create a folder within the reports folder, named in a way
that allows identification of the newly converted files, so as to prevent
confusion if names are similar to those in development in an existing project,
etc. Once cleared of exceptions detailed in the Task List and otherwise
tested, and perhaps renamed to fit our Reporting Services naming conventions,
they can be easily moved to another project, etc., before publication /
deployment in the routine manner.
Our reports are now in place and ready to be verified for
One of my favorite things about the Reporting Services
design environment, Report Designer, is that it allows us to see all the
parts involved in report creation, placed in a central, integrated place. We
can see data source information, dataset field composition, and
other items in a way that makes use of these objects a highly visual,
user-friendly experience. Coding knowledge is certainly not wasted, in any
way, but report developers need not be programmers to begin authoring reports
immediately. The more time we can invest in learning the package, obviously,
the better off we'll be, but the tools that Reporting Services provides us
typically give us numerous effective options for any given step in the report
One of the features I find extremely useful in the present
scenario, the conversion (and, more significantly, the enhancement) of
MS Access reports, is the existence of the Task List that is generated
as part of the conversion of the files to .rdl. In the lower half of
the design environment, the Output section logs messages based upon the Build
process that occurs to convert the reports, as it does for any report build (it
appears by default, although it can be hidden).
The Task List tab, located to the left of the Output
tab, allows us access to the Task List, which consists of "pending
items," as it were, that we can use to manage report components that need
to be upgraded manually, or otherwise modified to make the MS Access
features / characteristics that were supported in MS Access work in Reporting
Services. The Task List even has check boxes, where we can tick off
items as we handle them. Selecting Edit --> Select All enables us to copy this useful list
to an MS Excel or MS Word document, if that is useful, and we can
sort and otherwise manipulate the list items with a simple right-click within
the Task List itself.
A partial illustration of the Task List from the
conversion we undertook in the last section appears in Illustration 20.
Illustration 20: The Task List Generated from Our MS
Access Reports Conversions
Running a quick conversion of a set of reports, just as we
have done above, can thus enable us to create a list of items to be examined,
and modified (or perhaps even eliminated), in the original MS Access
database before performing a "for real" conversion later. Armed with
a list of "problem reports," we might want to upsize the reports with
minimal or no issues, and schedule the reports that we know will not upsize so
easily for complete rebuilding within Reporting Services, where the features
that do not readily convert can probably be better designed anyway. The
options are numerous, and I am confident that the Task List will be as
useful to many, as it has been for me.
Any successful conversion, particularly when managed via an "automatic
upgrade" process, such as the one we have witnessed in the conversion of
MS Access Reports to their Reporting Service counterparts, requires a degree of
professional skepticism. We certainly want to review our reports in the new
formats, and ascertain that, indeed, the exceptions noted in the conversion
process, and presented in the Task List, are complete and accurate. The
best way to do this, of course, will be to open the reports individually and
perform any interaction supported within the report, such as parameterization,
in real-world scenarios, to get a feel that all is functioning properly.
I would typically open a new report and run it against data
(say, for a closed month), using the original database / copy of the database
as a data source for the new report. I would then compare the results
presented in the new report to those given by its unconverted counterpart
(conveniently still in place in the MS Access database, whose reports we
targeted for conversion). If numbers agree in this and similar tests, and if
functionality has been tested and found to be complete and effective, the
report is ready to be placed into a test environment, just as we would do with
a report that we authored from scratch within Reporting Services.
We can examine our reports by taking the following steps within
our practice environment:
Summary of Sales by Year report, one of the reports that appears to have
converted directly, in the Reports folder for the project in Solution
from the context menu, as shown in Illustration 21.
Illustration 21: Opening the Summary by Sales Year Report
gather that this report converted seamlessly, because no error or warning
messages appear in the Task List, only that the report was successfully
imported. The Layout Tab for the report appears as depicted in Illustration
Illustration 22: The Layout Tab for the Summary of Sales
by Year Report (Compacted)
Click the Preview
tab to see the report as it appears with data.
of Sales by Year report appears, as partially shown in Illustration 23.
Illustration 23: The Summary of Sales by Year Report Design
Environment - Layout Tab (Partial View)
And so we see, from within Report
Designer, that the report has largely converted without significant
exception. We note that the data source is intact for the imported report, as well;
saving us time and effort in moving into the testing phases we discussed
earlier with our MS Access reports. Minor cosmetic modifications might certainly
be appropriate, but, again, the odds are that reports can be enhanced beyond
the capabilities of MS Access even with regard to simple appearances.
4. Close the report, as desired, by selecting File
5. Review the other converted reports to get a feel
for the results in each case.
6. Select File -->
Exit when comfortable with the
state of the reports.
prompted to save changes to the Project and the Solution files as
we leave the development environment, as depicted in Illustration 24.
Illustration 24: The Summary of Sales by Year Report Design
Environment - Layout Tab (Partial View)
7. Click Yes to close Visual Studio .NET.
In this article, we examined the
conversion capabilities built into Reporting Services for upsizing MS Access
reports. We began by introducing Reporting Services, focusing upon its Report
Designer development environment, and then discussed many of the realities
of conversion. We pointed out differences between MS Access and Reporting
Services, as well as enumerating many features and components that readily
convert, and many that require at least some intervention to remain useful.
Next, we began a hands-on
exercise with the preparation of a sample MS Access database for the import
process. We then performed the conversion of several MS Access reports to
Reporting Services. Finally, we discussed the need for verification of the
effectiveness of the conversion process via an examination of the newly
imported reports, as well as subsequent steps that might be taken to transition
the converted reports to the test environment and beyond.
See All Articles by Columnist William E. Pearson, III