In this tip, I'll offer how-tos on the primary tools needed for getting creative with reports from OpenOffice 2.0. I'll cover these topics:
You don't have to know SQL to get advanced reports from OpenOffice 2.0, and what you need to know about SQL is pretty simple. But SQL know-how will help you do the important parts. For instance, printing the total of all values for a field on a report requires simple SQL and multiplying two fields together; while very easy, these are technically SQL functions.
You're going to expand your report-writing options, if you think outside the wizard and are willing to do a little fiddling with some other windows. One allows you to drag the contents of a query or table into a Writer doc, and the other lets you insert fields from, about or which control, a table or query. I'll get into those further along in this article.
The Report Writer tool: A quick review
To use the report wizard, open the .odb database file containing the data you want. Click the Reports icon at left, then click the item labeled Use Wizard to Create Report.
Select the table or query (just one per report) that you want to use, and then insert the fields. If you need to combine fields from two tables or queries, you need to create a query containing them before you create the report. Click Next.
Follow the wizard through. At the grouping window, you can group the information by one or more of the fields.
When you're done, the report looks like this. The grouping is set up by Item ID, as I specified in the wizard. The grouping is a nice feature of the report writer; everything else is just the convenience of the wizard interface, and templates. (If you're able to add page numbers to footers, and you don't need clip art, the templates aren't that much of an advantage, either.)
The report wizard doesn't actually create any new data for you with no totals or averages. It's just about the arrangements. If there is anything you want to print that's beyond the data in the table, you need to accomplish:
Handling the Query Design tool and SQL View
When you enter information about a sale, or an employee's 401(k) contribution, you don't usually enter amounts that are calculations. You don't enter the unit price and the quantity, and then also enter the total amount. You don't enter the employee's salary and their 401(k) contribution percentage, or what actual amount that works out to. The software generally does the calculation.
If you want to be able to have calculated information on a report or just available in the database, you need to create that calculation in a query or view. Luckily, it's easy. Open your .odb database file, and click the Queries icon or the View icon. Then, either edit an existing query by right-clicking on it and choosing Edit, or click the option to create a query or view in design view. I'll be using queries in this example.
You'll see this window. For the purpose of this example, let's say I'm creating a new query.
Select at least one table and click Add. Then double-click each field that you want to have in the query just as is, reflecting the data without doing anything about it. (You don't have to -- your query can be entirely composed of calculations based on fields that aren't even in the query.)
I want this query to show the invoice ID, the date and the total amount of the invoice. So, I'm going to add that calculation. All I need to do, in the area where a field name would normally show, is to type fieldname*fieldname and press Enter. You could put double quotes around the fieldnames but you don't have to since Base will add them automatically.
If you want to, you can type an alias for the new field, like TotalAmount, in the field below the calculation.
Run the query, and it looks like this:
To add formatting, right-click on the fieldname and choose Column Format. Select the formatting you want from the window that appears, then click OK.
If you want to see this query or any query in SQL, just choose View > Switch Design View On/Off.
Of course, multiplication isn't the only thing you can do. Here are some very basic but useful examples. In each, when an alias is mentioned, creating the alias is optional. You can't easily refer to aliases in subsequent calculations, so the purpose of using an alias for the calculated field is to make your query or report look more professional.
How to handle report-specific fields
In the last section, I talked about how to sum all the values for a particular column using SUM. However, when you do that in a query -- well, it doesn't really work. A query shows multiple records. And SUM just gives you one value that applies to the whole set of values. It looks a little weird to see the total for the whole query next to every invoice ID.
Another issue is that SUM, at least in the designer, will not give you the sum of all the values, if you have any other fields in the same query. In this example, it works fine but there's just the one field, the sum of all the items purchased:
This doesn't show the total I want. It just shows the total per invoice.
So what do you do if you just want the total (or average, or maximum) for all the information in the report, just printed once at the bottom or top of your report?
You create a calculated field on the report document itself, using the fields usually reserved for creating forms. You can do this on reports created with the wizard, but not with the approaches covered in the next section.
Example:
Be sure that the Analyze SQL Command field is set to Yes.
Close the window.
You can create the fields at the top or bottom. Make sure to use AVG, MAX, MIN or other functions and so on.
How to print fast reports
Sometimes, you just want to print your table or query, and you want to print it now. Here's how to crank them out:
Fields
If you want the data to be fields that stays connected to the database, select the Fields option. Insert the fields you want, one-by-one. Type a space between each field, and press Return to go to the next line. Select a paragraph style if you want, then click OK.
The data will appear, usually with a message saying the data is incorrect. It's not; click the Data to Fields icon shown.
The data will appear correctly.
Text in a table
Select the Table radio button. Insert any fields you want, then select formatting options by clicking the Table or AutoFormat buttons.
Click OK and the data will appear. The example here shows the formatting for the autoformat I chose.
Choose Table > Table Properties to modify the table.
Just Text
Select the Text radio button. Select and insert the fields you want, adding spaces or carriage returns as necessary.
Click OK and the data will appear.
You can search and replace spaces with tabs, then set tabs to align correctly. Press Ctrl F, search for one or more spaces, click More and select Regular Expressions, and replace with /t.
How to create reports using the Next-Record Field
This approach is more useful if you have a specific layout you need to use, or if you simply prefer this approach. The drag-all-at-once approach from the last section is fine, but the layout doesn't look that good.
Let's say you want to print a list of information like this, with tabs between the columns but not in tables.
Here's what you do to get a nice layout, a connection with the database and multiple records on the same page. You drag out each field separately, separating with tabs or the like. Paste that row of fields into the next line and insert the Go to Next Record field in front of that second line. Then copy that second line all the way down the page.
Benefits of advanced OpenOffice tasks
Feeling a little tired? You should. This is a typical OpenOffice process, in that you can do lots of advanced procedures, but what to do is not obvious. In OpenOffice, there are usually several approaches, each with its own benefits and drawbacks. It's good to have a choice, but you have to know how to exploit the choices. These tips should give you some options.
Work is being done on Report Writer, and it will get more civilized. That's life on the frontier of office suites. My advice is to make the best of things until the report tool gets a bit more civilized. Enjoy the adventure of being a pioneer.
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.
Did you find this tip useful? Email us and let us know.
The upcoming Windows Server release will bring significant enhancements to Active Directory, including a new functional level and...
The company delivered one of its largest security update releases in recent years with a proxy driver spoofing vulnerability ...
Understand the costs and the requirements to use this flexible disaster recovery service that works with Linux and Windows ...
Errors can occur when an AWS developer builds a CloudFormation template, launches a stack or rolls back an update. Prevent and ...
CASB tools help secure cloud applications so only authorized users have access. Discover more about this rapidly evolving ...
Enterprises can be devastated by security-related weaknesses or flaws in their cloud environments. Find out where you are most ...
IBM Storage Assurance offers a new subscription model centered around on-premises hardware, while FlashSystem 5300 introduces an ...
A rejuvenated Hitachi Vantara, in product and leadership direction, is one of the many major vendors tackling the issue of how to...
Resilient storage has never been more important to an organization than it is now. Ensure storage systems can respond to issues ...
A green IT audit uses standards to help companies understand the ways an organization's tech practices affect the environment. ...
While the SEC's new climate rules and the EU's CSRD are both facing delays, businesses still need to identify methods for ...
A company's technology systems and devices can have a profound effect on sustainability efforts. Learn how a green IT assessment ...
All Rights Reserved, Copyright 2000 - 2024, TechTarget
Privacy Policy
Cookie Preferences
Do Not Sell or Share My Personal Information
source
