MS Access for the Business Environment: "Upsize" MS Access Reports to MS Reporting Services - Page 5

Verification

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 creation process.

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.

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:

We can 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 22.

The Summary of Sales by Year report appears, as partially shown in Illustration 23.

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.

We are prompted to save changes to the Project and the Solution files as we leave the development environment, as depicted in Illustration 24.

Conclusion ...

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