|Solveig Haugland, Site expert|
OpenOffice.org 2.0's database tool, called Base, may not change your life, but it will make saving and distributing data a lot easier. So, let's continue down easy street on our tour of Base's reporting tool, and I'll show you how to modify a report layout to your own taste and specifications.
I started my guide to OpenOffice.org 2.0's Base reporting tool in part one of this tip, describing how to create a simple report and modify the report layout.
Creating a report with groupings
Let's talk groupings for a bit. Groupings help out when you have one field that has a whole bunch of other things associated with it.
In a library, you might have one author who's written many books. In a business, you might (one hopes) have one customer to whom you've submitted many invoices. Instead of listing the author or customer multiple times, you use groupings to list it once, followed by all the associated information. Essentially, you set this up in the groupings window of the report.
The report I'm creating in this guide is for customers and invoices. I'm using the same Invoices table we used before, set up properly for that data. It starts off exactly the same way the simple report started, with one and only one table, query, or view. I's going to end the same way too. The only difference is in the groupings window.
Here's the grouping window. I've chosen the CustomerID field as the only grouping field.
In this window, you insert the grouping fields in the right side and leave the other fields on the left side.
The next graphic is a visual representation of this process. Let's say you just have one grouping level, customer IDs, and for each customer ID there is one or more Invoice ID, Status Field, and Invoice Amount. You'd take the Customer ID field and put it in the right-hand column as show in the following illustration. Then you'd get a report like the one on the right-hand side.
If you've got different data, you might want multiple levels. If you've got one customer, who has one or more invoices, each of which has one or more items, you'll want the customer and the invoice number fields to be in the grouping levels list. You'd set it up as shown, and have a report like the one on the right-hand side.
Get it? It's not that difficult, but it's visually kind of backwards of what you get in the report.
Once you've selected your grouping levels, click Next and you're on your way to the rest of the report setup. Do the rest of it exactly the same way you did the simple report.
Exactly the same way? Did I say that?
On second thought, there might be one other difference. Sorting might change, depending on your groupings. You probably want to sort by the first grouping field, then the second and your other choices might change to based on how you're grouping the data. It's up to you.
All right, I mean it this time: the rest should definitely be exactly the same. Continue through the wizard. Select the appropriate static/dynamic and create/modify options, and click Finish.
Modifying the report layout
Modify the report layout as you did for the simple report. Remember to check the footers and any other part of the report to be sure you're happy with the layout.
This is what the sample invoice report with groupings looks like, once I modified the format a bit.
Running the report
Just go back to the main database window and double-click the report. It's the same as for simple reports.
That's all there is to groupings. Insert your fields, so they look the opposite of the way you want them in the report, and sort the right way for your data and groupings. Then, remember your static/dynamic and create/modify options, and you're good to go.
Overview of editing the report layout
Speaking of static/dynamic and create/modify options, let's recap those briefly.
You can edit report layouts in a couple different ways. It's important to edit using the right approach for each report type, static reports and dynamic reports.
In static reports. Never choose to edit the report layout in the last window of the wizard. You won't get any data if you do that. Always choose to run the report right then and there. You want to make these selections.
To edit the layout, go back to the main database window. Find the report, right-click on it and choose Edit. You can edit the report now all you want.
Be sure to save the database when you've got the report how you want; in the main database window, just choose File > Save.
In dynamic reports, you can certainly use the same approach used for static reports. However, it's fine in the last wizard window to choose to edit the report, rather than running it.
To run the report to see the data, go back to the main database window. First, save the report. It's not saved yet. In the main database window, just choose File > Save. Find the report, and either double-click it or right-click on it and choose Open.
Final thoughts on reports
Reports are reasonably straightforward -- what I showed you, at least. I must confess, I experienced a few more crashes than I would really like. That's how I discovered that just because you've created a report doesn't mean it's saved. You have to save the database that the report was created with. Just choose File > Save, or when you close the database, choose to save changes.
What I ever so cleverly left out up to now, of course, is calculated fields. What if I want to list the total amount owed for the customer's invoices per customer and the total amount of all invoices on the report?
That's my cliffhanger, my "Who Shot J.R.," for this article. In another article, coming soon, I'll dramatically reveal how you would get that kind of data out of your database and into a report.
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.