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