Tip

OpenOffice 2.0 tutorial: Using SQL View, Query Design and more with Report Writer

Learn how to use the Query Design and SQL View tools, handle report-specific fields, print fast reports and more in this tutorial on OpenOffice's Report Writer.

In OpenOffice 2.0 Reports, it's easy to do the simple stuff, like just printing the contents of a table or query. It requires no SQL know-how at all, just the ability to press F4 and click and drag. Creating interesting and more informative reports isn't hard; but you must be willing to step away from the wizard.

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.

OpenOffice copy 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.

OpenOffice wizard query

Follow the wizard through. At the grouping window, you can group the information by one or more of the fields.

OpenOffice type formatting

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.)

Report writer grouping

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:

  • before creating the report, in the query
  • or after creating the report, with a calculated field.

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.

UnitPrice QuantityPurchased TotalAmount
[entered]
[entered]
[calculated]

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.

OpenOffice .odb 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.)

OpenOffice query add table

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.

OpenOffice fieldname calculation

If you want to, you can type an alias for the new field, like TotalAmount, in the field below the calculation.

new field alias

Run the query, and it looks like this:

OpenOffice running query

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.

OpenOffice formatting query

If you want to see this query or any query in SQL, just choose View > Switch Design View On/Off.

OpenOffice Design view SQL query

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.

Task In Design View (syntax) In SQL (syntax) In SQL (example)
Concatenate two fields "fieldname"+"fieldname" (fields will be added if they are both numeric values) "fieldname" + "fieldname" AS alias "InvoiceID" + "Date" AS "FullIdentifier"
Add the value of one column in one record to the value of another column in the same record Same as concatenation; just be sure that both fields are numeric.
Multiplication, subtraction, and division Same as addition. Be sure both fields are numeric.
Sum the value of every record of one field. Select the field and in the Functions line, select the SUM function. (If there are other fields to the left of this field, select GROUP as the function for all of them.) Select SUM(fieldname) FROM tablename AS alias SELECT SUM( "Quantity") FROM "Invoices" AS "Invoices"
Get the average maximum, minimum,or count of the values in a column Same as Sum, but use the appropriate function: MAX MIN COUNT AVG

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:

OpenOffice SUM query

This doesn't show the total I want. It just shows the total per invoice.

SUM 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.

  1. Create your report.
  2. Close it and save it. Click the Save icon in the report database. The right-click on the name and choose Edit to open it again and edit the layout.

    edit report layout

  3. You'll see the report:
  4. OpenOffice report view

  5. Choose View > Toolbars > Form Controls. If most of the icons are dimmed, click the Design View icon to switch to design view so you can use the controls.
  6. design view form controls

  7. Click the More Controls icon. Then in the More Controls toolbar palette that appears, click the Numerical Field icon. You're going to draw a field.
  8. more controls toolbar palette

  9. Scroll over to where you want the field. Draw it, making sure to draw it outside the table.
  10. drawing field in table

  11. Right-click on the field and choose Form (not Control -- not yet.)
  12. In the Form Properties window, click the Data tab and type the following command:
    Syntax
  13. Select SUM (field you want to sum) as the name you want to give this field from the query or table that the report is based on.

    Example:

    Select SUM (TotalPrice) as TotalForReport from InvoiceWithTotalPrice

    SQL SUM command

    Be sure that the Analyze SQL Command field is set to Yes.

  14. Close the window.
  15. Right-click on the field and this time choose Control.
  16. In the Control Properties window, click the Data tab and type the name you gave this calculated field. In this example, it's TotalForReport.
  17. Numeric field properties

  18. In the General tab, specify a thousands separator, if you want, then set any formatting options you want.
  19. Numeric field properties formatting

    Close the window.

  20. Close the report, saving the changes, and then click the Save icon in the database window. This is important. If you don't save before re-running the report, you won't see the field.
  21. Double-click the report name. The report will open and you'll see the field. Re-edit the report as necessary to change column widths, adjust the formatting of the field, etc.
  22. report database 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:

  1. Create a new text document.
  2. Press F4.
  3. Expand the database you want, the Tables or Queries item, and select the table or query you want.

    text document with database query

  4. Click the gray square at the left of the first fieldname. Click and hold down on it, and drag it into the document.
  5. fieldname selection

  6. The following window will appear. Make the selection based on what you want in the report.

    insert database columns

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.

database connected fields

The data will appear, usually with a message saying the data is incorrect. It's not; click the Data to Fields icon shown.

Data to fields view

The data will appear correctly.

correct query data

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.

table field insert

Click OK and the data will appear. The example here shows the formatting for the autoformat I chose.

autoformat table properties

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.

text specific fields

Click OK and the data will appear.

autoformat table properties

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.

[use the graphic birthdays.jpg  or the following table, either way.] 
Name Address Birthday
John Bertram 401 East Mulberry June 21, 1964
Miranda Worthington 12 Ludlow April 9, 1971

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.

  1. Create a new text document and press F4.
  2. Click the + icon to expand the database and table or query you want to use.
  3. Create some headings and set the tabs as appropriate. And, for a reason that will become clear in a second, press a tab before you type the first heading. It should look like this.
  4. tab heading appearance

  5. Click on the first field you want -- the title of the field, not the data. Drag it into your document under the first line of headings.
  6. document to data field

  7. Press Tab and drag the next field into the document. Keep going until you're done.
  8. data field document drag

  9. Select the entire line. Copy it, press Return at the end of the line and paste it. You now have two lines of fields, and all lines have a tab preceding them.
  10. Pasted field lines

  11. Click at the beginning of the second line of data, before the tab.
  12. data tab selection

  13. Choose Insert > Fields > Other. In the Databases tab, select Next Record in the Type column, then select the database and table or query you're using, and click Insert.
  14. field database selection

  15. The field appears, but only as a faint gray field. This is why you created the tabs, so that you could see the inserted Next Record field. If you insert it next to another field, it's difficult or impossible to see, and it's hard to be certain you've selected it.
  16. inserted next record field

  17. Now, it's time to copy that line down through the rest of the document. Copy the line with the Next Record field preceding it, and copy it until you've filled the document.
  18. copied next record field

  19. Click the gray square indicated, to preview your data.
  20. preview document data

  21. Click the Data to Fields icon. You'll see the data in the document. The gray Next Record field won't print.
  22. data to print field

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.

Dig Deeper on Data center ops, monitoring and management

SearchWindowsServer
Cloud Computing
Storage
Sustainability and ESG
Close