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

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.

[IMAGE]

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

[IMAGE]

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

[IMAGE]

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.

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

[IMAGE]

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.

[IMAGE]

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

[IMAGE]

Run the query, and it looks like this:

[IMAGE]

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.

[IMAGE]

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

[IMAGE]

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

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

[IMAGE]

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.

   [IMAGE]

3. You'll see the report:

[IMAGE]

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

[IMAGE]

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

[IMAGE]

6. Scroll over to where you want the field. Draw it, making sure to draw it outside the table.

[IMAGE]

7. Right-click on the field and choose Form (not Control -- not yet.)
8. In the Form Properties window, click the Data tab and type the following command:
9. 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:

   [IMAGE]

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

10. Close the window.
11. Right-click on the field and this time choose Control.
12. In the Control Properties window, click the Data tab and type the name you gave this calculated field. In this example, it's TotalForReport.

[IMAGE]

13. In the General tab, specify a thousands separator, if you want, then set any formatting options you want.

[IMAGE]

Close the window.

14. 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.
15. 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.

[IMAGE]

You can create the fields at the top or bottom. Make sure to use AVG, MAX, MIN or other functions and so on.

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.

[IMAGE]

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

[IMAGE]

The data will appear correctly.

[IMAGE]

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.

[IMAGE]

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

[IMAGE]

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.

[IMAGE]

Click OK and the data will appear.

[IMAGE]

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.

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.