|Solveig Haugland, Site expert|
With databases, it's important not just to put things in correctly, but to get them out correctly. You can slam data into your database all day long, but if you can't eventually print it out for your employees, your accountant, or that nice man from the IRS, you're not playing with a full database.
Luckily, the new database tool in OpenOffice.org 2.0, called Base, makes it put data in and get it out correctly. Base has a reporting tool with a built-in wizard. This means you can print out any data you want, from any single table, query, or view.
Here's my tour of the Base reporting tool. In part one, I'll describe how to create a simple report and modify the report layout. In part two, I'll tie it all up with an overview of how to modify a report layout to your own taste and specifications. Along the way, I offer lots of screen shots to illustrate the process.
What you gain from Base reports
Given OpenOffice.org's built-in PDF capabilities, it takes only a few steps to get any data out of your database and into a universally readable format. If you must, you can save the generated report as a Word document, too.
Here's what a very simple report in OpenOffice.org looks like, in the report document.
If that report isn't quite how you want your reports to look, don't worry; you have complete control over the formatting of the reports. You get some templates to choose from, but once the report is built based on one of those templates, you can edit the document itself and specify fonts, headers and footers, how much space each piece of data is displayed in, whether there are borders between the pieces of data. You'll be able to handle pretty much everything you can edit in a document.
You also get to create groupings, or different levels in your report. If you've got an invoices report, and one customer has a bunch of outstanding invoices, you list first that one customer, then all his or her invoices below that. On the other hand, if you don't want groupings, and you want every record on a single line, you can skip groupings.
Here's a little representation of different types of report groupings you could have; just plain, one level of groupings, or even two or more.
The process of creating reports is fairly simple, and the wizard is pretty straightforward. However, there are a few things which aren't obvious about how to get data from two tables into a report, static versus dynamic reports,and how to edit the layout. I'll note them clearly in the procedures.
So, let's get to work on a basic report from one table, with no groupings or anything fancy.
Creating a simple report
At this point, I'll assume that you have a database and a few tables.
To create a report, just go to your database in OpenOffice.org by choosing File > Open.
In this example, I'm going to do a report on the Invoices table with this data.
Select the Reports icon, and click the option for creating a report with the wizard.
<>p>The wizard appears. In the first window, you choose which table, view, or query your report is going to be based on. I'm just going to do the report on the Invoices table, so I selected that one and inserted all the fields.
Note: You can't pick more than one, so if you want to create a report based on two or more tables, you need to create a query or view with the right tables and fields.
Click Next to specify what labels are in the report. You might be happy with the name CustomerID or Status in your tables, but you probably want something clearer, more explicit, or with spaces between words in the report. Just type what you want to appear in the report next to each field.
Click Next to see the grouping window. We're skipping this for now, so just click Next again.
In the sorting window, you choose what order the report is sorted in. This is very straightforward, just like what you've seen in the spreadsheet sort window and elsewhere. Select at least one field to sort by and Ascending or Descending.
Click Next to see the formatting window. There are quite a few different layouts and designs to choose from. Unless you find one you really like, or unless you're really into the cute icons from some of the designs, you might be happiest just leaving both columns at Default and modifying the report layout manually later.
Oddly enough, the Table toolbar pops up, even though you can't use it now. Ignore it. You can't move it, but you can move the wizard window around.<[/p>
Click Next to see the last wizard window, where you choose the type of report, and whether to create it or modify it.
This window is a bit tricky, so make sure you know what you want, and make the right selections. Here's what the options mean:
- Static versus dynamic – A static report will always contain the data that's in the table view or query right now. A dynamic report will go back to the source data each time you run it.
- Modifying layout versus creating report – You can choose to edit the layout now (you probably want to edit the layout at some point) or to just run the report.
Here's what to do:
You can read more about editing report layouts in the summary at the end of this article.
I'm creating a dynamic report, and I want to edit the layout, so I choose Modify Report Layout, Dynamic Report, and click Finish.
Modifying the report layout
Here's what the report looks like in edit mode.
Note: The dates are sometimes randomly wrong in edit mode. I find the year reverts to 2016. Don't worry, as they'll come out correctly when you run the report. Of course, by making that statement, I pretty much assure someone's dates will be screwed up. But in virtually all cases, the dates will come out fine. Knock keyboard.
Note: The forms toolbar pops up when you go into edit mode. Just close it.
I changed the report layout around a bit. I changed the width of the columns, updated font and font color and so on. Then I saved it, and closed it.
Running the report
To run the report, go back to the main database window and just double-click the report name. Here's what the report looks like when I run it.
That's pretty much all there is to creating a report, making it look the way you want, and running it again.
Now, let's move on to creating a report with groupings. Continue to part two.
Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 15 years, for employers including Microsoft Great Plains, Sun Microsystems,and BEA. Currently, Solveig is a StarOffice and OpenOffice.org instructor, author, and freelance technical writer. She is also co-author, with Floyd Jones, of three books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published
by Prentice Hall PTR. Her fourth book, on OpenOffice.org 2.0, is coming this summer. For
more tips on working in OpenOffice, visit Solveig's OpenOffice blog.
This was first published in February 2006