Producing Standard Reports

Almost every database system has a certain number of reports that you can define in advance. You will have discovered most of these standard reports during the work process analysis, but it's worthwhile spending some time going over the database schema with users to consider whether any other reports would be useful to them.

Listing Reports and Detail Reports

For each entity in the system, consider using both listing reports and detail reports. A listing report is just a list of every instance of the entity—that is, every record in the table. Sometimes you can simply arrange these listings in alphabetical order. More often, you should group listings in some way. Customers might be grouped by state, region, or salesperson, for example. If you expect a report's source table to contain more than a few hundred records, provide users with the option of printing only a selected range of records. A salesperson is most likely going to want a list of only their own customers, for example, not of every customer in the system.

Whereas a listing displays a few details for every instance of an entity, a detail report shows all (or at least most) of the details about a specific entity. Again, you'll usually want to provide some way for users to select the records to print. A multiselect list box is often a good mechanism for selecting records because it doesn't require contiguous selections.

Remember to bear in mind the practical limitations of list boxes, however. If the table contains thousands of records, you must use a set of list boxes to allow users to progressively narrow the range of records. Alternatively, you could use some other type of control. You might consider using a text box similar in functionality to the one used by Microsoft Word in the Print dialog box to specify print ranges, for example. Parsing a range of records separated by a dash, or single records separated by commas, is not difficult to implement.

Summary Reports

A little more complicated to implement than listing and detail reports, but often more useful, are what I think of as "slice and dice" reports: summary data combined and compared in various ways. The percentage of sales by region or salesperson and the number of customers who purchase each product category are examples of this type of report.

Summary reports are good candidates for graphical representation, and Microsoft Graph and various third-party tools make the implementation of graphical reports straightforward. However, I recommend that you provide graphics in addition to textual data rather than instead of it. A text-based sales summary might not be the easiest thing in the world to look at, but it can be exported to a statistical analysis tool or a spreadsheet such as Microsoft Excel for further manipulation.

Form-Based Reports

In addition to the reports derived from the database schema, consider also the forms in the system as a source of useful reports. I provide the ability to print copies of the majority of forms in a database system as a matter of course. They're easy for you to implement and extraordinarily useful to users for checking their work or getting a quick printout to show to someone else.

Sometimes an entity detail report contains the same information as a form, and you can use the detail report rather than creating a new form report. Most often, however, entity detail reports contain additional information or are formatted differently than a form report. Reports based on forms are so cheap and easy to provide that I usually give my users both detail and form reports. The form-based report is the default printed when a user clicks the print button on the toolbar, and the detail report is available from a menu (and perhaps also from the toolbar).

Reporting Interfaces

Making reports available within the user interface is not very tricky. If you use Print Report as a command, you have three methods for making the command available to users: via a menu, a toolbar button, or a command button on a form. A command that prints a report must always make clear to users which report is to be printed.

Indicating which report will print is easy to do on a menu: you can use the report name as a Reports menu item. You can use the report name as a toolbar button's ToolTip or a command button's caption as well, but you should preface the name with the verb "Print". "Customer Listing" is sufficient as a menu item on a Reports menu, for example, but the corresponding ToolTip or command button caption would be "Print Customer Listing". In addition to being compliant with Windows guidelines, this text makes it clear to users that the system will print a report, as opposed to opening a window containing the report.

In addition to where you make the system's reports available to users (via a menu, a toolbar, or a command button), you must also consider how. A database system often contains dozens, even hundreds, of reports and listing them all in one huge umpteen-column menu is obviously not sensible. Fortunately, limiting the list of available reports to those that make sense within a user's current context is usually easy to do. A user is unlikely to want to print a list of employee phone numbers in the middle of entering sales orders, for example.

If you think making all the system reports available at once is appropriate, you could provide a dialog box that presents a categorized list of the reports and allows users to choose the report they want to print. This approach can also be useful if users need to print multiple reports at once. By allowing them to select any number of reports at once on the report dialog box, you enable them to click the print button once and then go about their business.

For reports that are often printed as a set—such as month-end summaries—I use a variation of the dialog box list technique. I present a dialog box with all the reports that are usually printed as a set selected by default. Users can add any relevant reports that are printed only occasionally, or deselect a standard report, before sending the whole set to the printer.

Listing each report in a set imposes a little more overhead on users than batching the reports from a single menu item does, but I find that the additional flexibility for users is generally worth the single extra mouse click involved. A single report in a batch often needs to be reprinted because of a printer fault or because somebody spilled coffee on it. A dialog box that groups these report sets but allows each report to be printed individually eliminates the need to list each report on a menu, or worse, print an entire report set because there's a problem with only a single report.

Handling Printer Errors

Your system must always be able to handle printers messing up or printouts being messed up, and because of this, certain common printing situations can be very tricky to handle. A user might want to print all the invoices that haven't already been printed, for example. This common requirement is one of the most difficult for a system to handle gracefully because it can't know whether any given report has actually been printed. The system knows only that it sent the report to the print spooler, and that's not the same thing at all.

Some designers handle possible printer errors by displaying a message box after the reports have been sent to the printer, asking for confirmation that the reports printed successfully. This approach will work, but it requires that users stop using the system until they have the printouts in their hand. If the print job happens to be behind somebody's 1000-page manual in the printer queue, a user could be in for quite a wait. Furthermore, since most reports do print correctly the first time, the delay is unnecessary 99 percent of the time.

I prefer to handle printing problems as the exception that they are. To return to our earlier example, if the system needs to print only unprinted invoices, you'll need to add a field to the appropriate table anyway. If the system is relying on user confirmation that the invoices have printed successfully, all that's required is a Yes/No or Boolean field. But it's easy enough to store a date or print job number instead. You can then add a command that allows users to record printing problems on either a print-job or an invoice-by-invoice basis.

If a report is printed no more than once a day, you can use the current date as a flag. It's safer, however, to generate a unique number for each print job and store that instead. If anything goes wrong, a user simply selects the appropriate print job and the system can set the fields containing that print job number back to Null. The records containing a Null will automatically be included in the next print job. Alternatively, the system can display all the records that were included in the problem print job and allow the user to select only specific records to be reprinted.

How does a user recognize a particular print job? You could include the print job number in a report footer, but I prefer to set up a system table that stores the name of the report, the print job number, the date on which the report was printed, and (if it is available) the name of the user who initiated the job. You can then present a descriptive list of print jobs to users rather than making them remember a meaningless number.

Sometimes more is at stake than whether a given record is included in the next print run. Accounting systems sometimes include report generation as part of end-of-month processing, for example. Once the month is rolled over, certain values will be reinitialized, and there might be no way (or at least no easy way) of regenerating the reports. I think this is an extremely poor design strategy.

Because of the unreliability of printing, I try very hard to keep report generation and record updates (other than "record printed" updates) as completely separate tasks, and I recommend you do the same. If the work process absolutely requires that updating a record be linked to printing a report, the safest approach is to hold up further system processing until the person printing the report confirms that the report has printed successfully.

Confirming a successful print job might be possible to do as a background process, so you don't hold up the system until the job is confirmed. You could, for example, place an icon in the status bar that, when clicked, displays the confirmation dialog box and completes the table update. Be sure to display a message to users explaining what they need to do.

Automatic and On-Demand Printing

Another consideration in producing standard reports is whether they should be printed on demand, automatically, or both. As a general rule, I make all reports on demand. The only exception I make is for a report that is clearly part of a work process, such as an invoice produced after a user enters a sales order. Such an automatic report should be available on demand as well, in order to deal with those pesky printer problems.

Note that reports tied to a work process, such as invoices, can be printed one at a time or as a batch. For example, you can print each invoice as the corresponding sales order is entered or you can batch all the unprinted invoices and print them at the end of the data entry session. I've used both approaches successfully. I tend to print each invoice individually if users have a local printer and to batch print invoices on a network printer. You should probably make the choice configurable by users—printer setups are subject to change.

Some designers choose to have the system automatically produce reports that are printed at regular intervals—weekly or at the end of each month, for example—but I prefer to make these on demand as well. Having the system produce reports automatically is a complex task. The system must calculate when the report needs to be printed (allowing for weekends and holidays) and track whether the report has yet been printed on the correct date. If multiple people use the system, it must also determine which user or category of user should trigger the print job, and what to do if no users of that category log onto the system on the required date.

Compared with all the problems associated with automatic report generation, having the user select Print Weekly Reports from a menu at their convenience is trivial. In any event, you'll need to provide the menu item (or a reporting problem dialog box), since users will need to be able to regenerate the reports in case of printing problems.

Users aren't very likely to forget to print the weekly, monthly, or quarterly reports. However, there's no harm done if you allow users to specify the period for which the report is to be run, in case they do forget. By using the current period of time as a default value and allowing users to change the value, you enable them to easily correct any oversights. This technique also allows reports that are printed at regular intervals to be printed before the end of the current time period. To be able to check performance against budget while there's still time to do something about potential cost overruns can be useful.