Producing Ad Hoc Reports

The work processes a database system is intended to support are sometimes extremely well defined, and you can specify in advance all the reports the system must produce. More often, however, you will need to provide some level of flexibility for users to configure or design reports themselves.

Just how much flexibility is required must be determined by the needs of your users and will vary from system to system. The degree of flexibility can range from providing users with a full-blown report designer to simply allowing users to specify additional filter criteria for predefined reports.

Report Designers

Providing users with a report design tool is an easy option to implement if you can use a commercially available tool such as Access or a third-party control. (The new Microsoft Data Reports provided with Visual Basic 6 aren't really appropriate, since they require the development environment to be present on each user's machine.)

Report designers give users essentially unlimited freedom in designing whatever reports they need. Unfortunately, this flexibility has a price. It's not so much that the base cost of the reporting tool is high, although if you provide a full copy of Access to hundreds of users who otherwise don't need it, the base cost is certainly not insignificant.

The cost is more the result of having to train users to design reports using sophisticated tools. Not only must users know how to lay out the report using the report designer, they must also have at least a superficial understanding of the database schema to access the specific data they need. None of this is beyond the capabilities of the average user given adequate time and training. But these people already have a job, and building custom reports isn't it.

Customized Report Design

To make the report design process efficient for users, implementing a custom report design utility is often more appropriate than using a commercial tool. Theoretically, it's possible to provide a report design tool that has the same flexibility as the Access report designer and is customized to support the database schema of the system. Such an exercise would be expensive, however, and would not be justified in most situations. A more usual solution is to provide a set of predefined report layouts and allow users to specify the data to be displayed.

The Access Report Wizard is probably still too close to the implementation model for customized reporting, but it is an example of a useful approach to providing predefined layouts. The Report Wizard provides extensive formatting capabilities but avoids much of the complexity of report design by allowing users to select from a list of predefined report layouts and styles. Once the Report Wizard generates the report, users can further manipulate it using the Access interface.

Allowing users to specify the layout and style of a report separately provides a fine degree of control to users. Alternatively, you can combine layout and style in various ways to simplify the process. Most users require "custom" reports that are really only variations of standard reports. In other words, they need reports that are "just like this, but..." and what follows the "but" is either sorting or filtering criteria.

The taxonomy I use in my own work is therefore quite different from the one used by the Access Report Wizard. I divide an ad hoc report into two components, which I call the "format" and the "criteria." The format rolls together the layout and style components of the report and also specifies the fields (and by extension the table or query on which the form is based) to print. It is, in fact, an Access Report or Visual Basic Data Report object that is modified at run time based on the criteria specified by the user. The criteria specify the sorting and filtering to apply to the format. I almost always provide the user with a means of storing and reusing criteria. We'll see how this works a little later. Allowing users to define grouping levels is sometimes appropriate as well, but as a general rule I have found this unnecessary.

Because users are specifying only two components, I use a dialog box for generating custom reports. A general structure for this dialog box is shown in Figure 17-4. (If you adopt this approach to ad hoc reporting, you should modify the sample to conform to the user interface of your system.)

Figure 17-4. I use this basic structure for providing ad hoc reporting to users.

The dialog box is divided into three sections. In the left pane, a tree view control presents users with a list of formats to choose from. You could use a list box or even a set of radio buttons rather than a tree view control. In the example, the formats are grouped into categories. This technique is useful if you have a lot of formats. Grouping the formats into "Administrative Reports," "Month-End Reports," and "Sales Reports," for example, makes it easier for the user to find the report they want.

The third level of the hierarchy shown in Figure 17-4 are "reports." A report, in this context, is the combination of a saved criteria and format. Having specified "Southwest" for the Region in the criteria of a sales report format, for example, the user can save a report as "Southwest Region Sales." If you have complex criteria specifications, this capability can save users a great deal of time.

The middle pane of the dialog box allows users to specify the sorting and filtering criteria. You can sometimes use a single set of controls in the criteria pane for all formats, although it might be necessary to disable certain controls that don't apply to the format a user has chosen. Sometimes each format will require a completely different set of controls for establishing the criteria. In my work, I tend to walk a middle path. As shown in Figure 17-4, I divide the report formats into categories, and it is the category that determines the criteria controls.

The right pane of the dialog box in Figure 17-4 contains a set of command buttons. The Print button displays a subsidiary dialog box allowing users to set print options, such as the number of copies or the printer. If these options aren't available, it's probably better to provide two command buttons, Print and Print Preview, and save the user the additional step of displaying a dialog box.

The Save Or Restore Criteria command button displays a dialog box that would be similar to that shown in Figure 17-5. This simple dialog box provides the same criteria controls displayed on the main custom reporting form in the middle pane. The criteria that users have already saved are shown in a list box in the left pane. Selecting a criterion in the list box displays it in the middle pane. The Save As... button first asks the user for a name and then saves the criterion specified, while the Restore button loads a criterion into the main form.

Figure 17-5. This dialog box, called from the main custom reporting form, allows a user to save and restore report criteria.

The ability to save criteria in this way is quite straightforward to implement. You simply need a table for each category, with fields for each control on the pane. In a multiuser situation, you need to decide whether to have the saved criteria be available to everyone or allow each user to maintain his own set. If the criteria are shared, the tables should be kept in the main database along with other shared data. If each user maintains his own set of criteria, the tables should be stored locally, in the front-end database (or a local database if your application is written in something other than Microsoft Access).

The two techniques are not mutually exclusive. You can easily provide both shared and user-specific criteria either by including the user's name in the criteria table or by displaying a UNION of the shared and local tables. I usually include the user's name in a shared table because it makes it easier to support "roaming" users, who may use more than one computer to access the system.

Saving and restoring criteria allows users to save criteria on a category basis. The criteria thus saved can be applied to any report in that category (assuming that the reports share criteria specifications).

Sometimes, however, it's more appropriate to link the criteria with a specific report format, which is what the final command button in Figure 17-4 does. Again, this is straightforward to implement. You will need tables for each type of criteria (you can reuse the tables used for saving criteria if you've implemented that functionality) and a table that links the report formats and the criteria. The structure is shown in Figure 17-6.

Figure 17-6. This table structure allows users to save criteria and reports and has the added benefit of allowing reports to be added to the system at run time.

The ReportCategories table contains a field called CategoryTableName. This allows the system to identify the specific criteria table that contains the criteria for that category of format. If all your reports have the same criteria structure, this field isn't necessary. On the other hand, if all your formats have different criteria structures, this field should be in the ReportFormats table.

The table structure shown in Figure 17-6 also allows the reports available within the system to be configured at run time, which is what the FormatName and PhysicalName fields in the ReportFormats table are all about. If your report formats are defined independently, either as objects in the database (as with Access reports) or as separate files (as with many third-party reporting tools), you can use indirection to enable adding reports to the system at any time. To do so, you base the list of formats shown in the form dialog box on the ReportFormats table rather than hard-coding the list.

To add a new report format, you need only create the format object (a report in Access, or a separate operating system file) and then add a record to the ReportFormats system table. The new format will automatically be available within the system without anyone having to touch core system functionality. The FormatName field contains the text to be displayed to the user, while the PhysicalName contains the actual name of the object and perhaps its file path if the objects are stored externally.

The approach to custom reporting described here, while simpler for users than a full-blown report design tool, is overkill for some applications. All your users might need is the ability to occasionally specify additional filter criteria for the predefined standard reports. You can often accommodate these simple criteria by adding a few controls for specifying the criteria to a custom print dialog box, rather than implementing a full custom reporting user interface such as the one I've described.

Standard Letters

A special type of custom report is the standard letter. Sometimes the text of standard letters is fixed, but more often users need to select from boilerplate paragraphs and have the system combine them to create the letter. Whether the text is fixed or not, I am of the opinion that a database report is not the best means of producing correspondence.

Although database report formatting capabilities are becoming increasingly powerful, they do not equal the formatting capabilities of a word processor specifically designed for this task. Furthermore, most users want the ability to add text to letters before they are printed, but allowing them to manipulate the database reports is not a good technique because any changes to the standard structure would be permanent.

By all means, use the database to maintain names and addresses, and even to store the boilerplate paragraphs. But outputting this data to a word processor, such as Microsoft Word, allows more sophisticated formatting and lets users manipulate the text before printing it.

Fortunately, sending standard letters to a word processor is becoming increasingly easy to implement. Gone are the days of struggling to send Dynamic Data Exchange (DDE) command strings to some uncooperative, poorly documented application. In Microsoft Word, for example, you can directly specify an Access table or query as the data source for a mail merge with a document. You can then use Visual Basic for Applications (VBA) to merge the document with the data from your database application and display the document to users for subsequent manipulation or send it directly to the printer.

In some circumstances, you might need to keep track of the standard letters that are created by the system. If you aren't allowing users to customize the letters before they print them, there's no need to store the letters themselves in the database. You need to store only the fact that a letter was sent, and perhaps the date and the name of the user who sent it. On the other hand, if users create the letters by combining boilerplate paragraphs, you can model this process using a complex entity, as shown in Figure 17-7.

Figure 17-7. This structure can store the paragraphs included in a specific letter.

If the system allows users to customize letters before they are printed, it's better not to store the text of the letter in the database, since neither Access nor Microsoft SQL Server is particularly efficient at handling large amounts of text. You need to store only the location and name of the physical document file. This means the system must be prepared to handle the file being moved, renamed, or deleted, usually by asking users for assistance.