Using the Sun Report Builder extension (http://extensions.services.openoffice.org/project/reportdesign), you can create rather sophisticated reports. For example, the miniInvoices solution (http://code.google.com/p/writertools/downloads/list)) relies on this extension for printing single invoices and generating all sorts of reports. The problem with using a separate extension for adding functionality is that it makes the entire database solution less portable. Move the miniInvoices.odb file to a machine without the Report Builder extension, and you loose the ability to print invoices and create reports. But what if you need to print, for example, a single invoice from the miniInvoices.odb database and you don’t have the Report Builder extension installed on your machine? Fortunately, you can solve this problem using OpenOffice.org’s built-in tools. Better yet, this alternative approach provides a good opportunity to learn a few clever OpenOffice.org tricks and can help you to make your database solutions more flexible. The following description uses the miniInvoices.odb database to create a single invoice, but the techniques it demonstrates can be used with any OpenOffice.org Base database.
First of all, you have to register the miniInvoices.odt database as a data source in OpenOffice.org. To do this, launch OpenOffice.org and choose Tools -> Options. Select OpenOffice.org Base -> Databases and press the New button. Select the miniInvoice.odb database and give the new connection the “miniInvoices” name.
Next you have to create a simple template which you can use to quickly generate a document containing the recipient’s address and insert invoicing data. Although you can skip this step, the template can help you to avoid typing address info every time you want to create an invoice. Moreover, the template also demonstrates how to use the nifty Data to Fields feature. In OpenOffice.org, create a new Writer document and choose Insert -> Fields -> Other (or use the Ctrl+F2 keyboard shortcut).
Caption: inserting mail merge fields
Switch to the Databases tab, select the Mail merge fields item from the Type list in the left pane. In the Database selection pane, navigate to the miniInvoices -> Publications table which contains customer info. Select then the Publication field and press the Insert button. Repeat this for the Address_Street, Address_Code, Address_City, Country, and other fields you might want to appear in your template. Close the dialog window, and you should see the inserted fields in the Writer document. You can move fields around and format them the way you like. Save the file as a Writer template (i.e., .ott file).
Now double-click on the saved .ott template to create a new empty document. Press F4 to open the Data Sources pane which has two sections: Explorer to the left, and Record Browser to the right. Select miniInvoices -> Tables -> Publications in the Explorer section. You should then see the existing records in the Record Browser. Select the record containing the address info of the desired recipient and press the Data to Fields button on the Table Data toolbar. This inserts the actual data in the fields in the document.
Next step is to add invoice items to the document. To do this, select miniInvoices -> Queries -> Invoice_w_Parameter and enter the desired invoice number when prompted. To select multiple records, press and hold the Ctrl key while selecting the records you want. Press then the Data to Text button on the Table Data toolbar. This opens the Insert Database Columns dialog window. Choose the Table option and move the columns you want to insert from the right pane to the left one (e.g., Article title and Amount). To add column headings to the table, tick the Insert table heading check box and choose the Apply column name option. If you want to apply some fancy formatting to the table, press the AutoFormat button and select the theme you like. Alternatively, you can press the Properties button and tweak the table’s settings manually. Finally, press the OK button to insert the table with the records in the document.
Caption: Inserting fields in the document
Your invoice is almost finished, but there are few other things you might want to add to it. For example, you can add a row containing the total amount payable. To do this, place the cursor in the last row in the inserted table, and press the Insert Row button in the Table toolbar (or choose Table -> Insert -> Rows). Place the cursor in the empty cell in the Amount column and press the Sum button on the Table toolbar. This opens the Formula bar with a Sum calculation filled in. Press the Apply button on the Formula toolbar, and you are done.
The described approach does require some manual work to create a printable report, but it can save your bacon when you don’t have the Report Builder extension handy. Moreover, it gives you more flexibility, as you can easily tweak the invoice layout on-the-fly. Finally, this technique provides a good example of using less-known yet very handy OpenOffice.org features which allow you to insert data from a database in a Writer document.
by Dmitri Popov of Nothickmanuals.info