Creating Reports in OpenOffice.org Base

Let's face it, databases are only minimally human-readable. Or, at least, that's the case in OpenOffice.org Base, regardless of whether you view a database's tables directly or filter them through a query or form. In fact, to anybody who's not an accountant or a computer geek, reading databases can be downright intimidating. Reports are an effort to manipulate database records into a more friendly form, including preparing them for printing or for insertion into a larger document.

By the standards of other databases, such as MySQL or Oracle, Base has a limited report capacity. The designs of its reports are simple and not overly attractive. However, this limitation is partially compensated by the fact that reports in Base are quick and easy to create and are displayed and printed in Writer, which gives you tools to improve their layout.

Creating a Report

To create a report. open a database and select Reports from the Database pane, then Use Wizard to Create Report, and follow the steps outlined in the Report Wizard. If necessary, you can click the Help button for more information about the steps, although be warned that the help is not very detailed.

For that matter, nothing in Base or the on-line help makes clear exactly what you are doing in the wizard—namely, creating a layout to display existing data. You cannot use the wizard to run statistics on the data in the report, although with a little patience you can add a few basic ones to the report. You also might be able to create a query that will add the statistics you want.

The first step in creating a report is to select the table or query from which the report borrows its data. This selection affects the available fields for the report. Choose the fields to include by moving them from the Available Fields pane to the Fields in report pane. You can use the left and right arrows to move highlighted fields from one pane to another, and the up and down arrows to reposition fields in the Fields in report pane.

Clicking the Next button moves you to the Labelling fields page. This page is not strictly necessary, but it gives you a chance to display the fields on the report in a more human-friendly way. For example, you might want to change a field called CompanyName to simply "Company" or to add a space between the words in a field called LastName. Similarly, in a report on quarterly earnings, you might decide that "First Quarter" is more readable than "Q1." Note that none of these alterations changes the actual name of the field in the database, only how the field is labeled in the report.

On the third page of the Wizard, you have the option to arrange fields into groups and subgroups in the report. For most reports, you probably will want at least one group. For example, if the report is about the income that your business has derived from different customers, you probably will want to list quarterly income from each source by company. By contrast, in a more complicated report, such as a listing of a large corporation's sales for each quarter, you might want to break sales down by departments and sales reps. The highest grouping will be the field at the top of the Groupings pane, and, in most report formats, each subgroup will be indented a tab.

You then have the choice of arranging the order in which records are listed in the report. You can have up to four sorting orders, but in many standard cases, you will need only one, such as CompanyName. If you already have grouped fields on the third page, some or all of the fields on this page will be already filled in and unchangeable, for the obvious reason that grouping and sorting are almost the identical function.

On the fifth page of the wizard, you can select from predefined templates for the report. You have separate templates for the layout of the data and of headers and footers. Before you make your final selections, you can preview your choices with dummy text in the Writer window behind the wizard. Some of the available layouts come with rather cheesy clip art, so I suggest choosing a simple design. Any of the designs you choose also will include fields from your user information in OpenOffice.org.

Additionally, take time to choose the report's page orientation. If you have five fields or less, you probably can use portrait for the orientation. Any more than that (or if some fields have long strings for content), and landscape is probably a better choice, so that records don't spill over into more than one line and confuse the report's readers.

By the time you have reached the sixth page of the wizard, you have created the basic look and feel of the report. However, take the time to think about the name of the report and whether you want the report to be static (a snapshot of the current information) or dynamic (updating itself each time you open the report to use it).

Your last step in the wizard is to select whether you want to modify the template or create the report now. Either step takes you to a Writer window where you can continue formatting the report. The only difference is whether you want to modify the layout for all your subsequent uses of the report by editing the layout, or whether you want to view and modify only the current instance by choosing to create the report.

Editing and Using a Report

The wizard is by far the easiest way to edit a report in Base. For instance, although you easily can change the name under which each field is displayed in the wizard, in Writer, you have to double-click on a field and change its Value, taking care not to make a mistake and stop the template from functioning.

However, if you do want to make changes via Writer, select Reports in Base's Database pane, then right-click on the report and select Edit from the context menu to open the template in Writer.

You can use any of Writer's tools to edit the template. You might, for instance, prefer to have your company name appear in the header or footer, rather than your name, or to replace the cheesy clip art with a corporate logo or art more to your own liking.

Most of the information in reports is arranged in tables, so you also can use the floating toolbar to change the layout of the tables, altering the borders or adding different colored backgrounds to create a report branded for your company. You even can add rows to the table and take advantage of the few spreadsheet functions available in Writer's tables, such asSUM, to add additional data to the report.

When you are ready to use the report, return to Base, and after highlighting it, select Open from the context menu. When the report opens in Writer, you can print it separately or copy the report into another document, such as an annual report.

Conclusion

Sun Microsystems, which employs many of OpenOffice.org's developers, has released the Sun Report Builder, an extension that offers you both another tool for building reports and a selection of more contemporary-looking report templates. The fact that this extension is usually in the top five most popular extensions on the OpenOffice.org extension page tells you just how much of a need users feel for an improvement on Base's built-in tools.

Base reports are not the best-designed of their kind. They do their job, but they have a distinct early-1990s look about them. Start with them, and then, if you feel the need, graduate to Sun Report Builder.