Much easier, much simpler ways to print the contents of a database in OpenOffice.org Base

I've been so caught up in the complicated tools and cool hacks that I've forgotten about the really simple ways to get data from a database into a Writer document.

I'm so embarrassed.

Here's a simple way to bring in data, whether it's still in fields, or just plain text. You probably want to use this instead of the Reports feature, and instead of the Next Record field under Insert > Fields > Other, Database tab.

Here are some screen shots. I'll do more on this later but I just wanted to slap these options out there so you know about them.

Click any of the screen shots to see them bigger and in more detail.

1. Create a new text document and choose F4.

2. Click the + by your database, click the + by the word Table (or Query) and click the table or query you want to print.

3. Click the blank gray square shown, to the left of the first fieldname.

4. Click and hold down on that gray square and drag into the document.

5. Now you have various options.

Pick the option you want, insert the fields you want, apply any relevant formatting, and click OK.

Table

Fields

Text

Then print. If you bring in the data in fields, when you choose Print, click Yes when prompted and DON'T click the checkbox.)

Again, if you're printing in fields, you cna print to files or a printer. To select specific records, select the first record, hold down Ctrl, select another record, and so on.

 

 

---

---

Comments

[Bill Harris]

Solveig, that works nicely. Is there a way to make this dynamic, so that I can update the report when the database changes without blowing the table away and starting over?

I also tried your instructions on generating dynamic reports. That seems to work nicely, except that I end up with the report template, a dialog box saying it's accessing the database, and then an empty report (headers but no data). I can open the query on which it's based and see data; any ideas what might cause this problem?

[Solveig]

Hi Bill,

If you choose the Fields radio button, it will be dynamic. Just be sure all the fields are selected in the data window you got by pressing F4, then click the Data to Fields icon again to update the data in your document. In the 2nd illustration above in the posting for the Fields option, the Data to Fields icon is shown with a little tooltip.

Re dynamic reports, in the final window of the wizard you need to make sure that you don't select the Edit Report radio button. Generate the report first, then go back and edit it. I've ended up with a blank report if I try to edit, then generate.

Solveig

[Bill Harris]

Hi, Solveig. Thanks! I chose Table, needless to say; hitting that Data to Fields icon didn't do anything except give my fingers a bit of exercise. Doing a new report with Fields did the trick!

For a bit, I thought I could place "|" symbols between the fields when I set them up, convert text to table by splitting on "|", and then have an updated table, as I originally wanted. The data view in the top gets updated, but Data to Fields doesn't pick up when I add a row.

Oh, well. I'll figure out another way to format the "sea of fields" I get (4 columns by a couple of pages of rows, currently).

Thanks again!

[Bill Harris]

I tried the dynamic report again and discovered something interesting. It works great when I report on a table. When I report on a query, it gives me the dummy report with lorem ipsum text or with no data at all. Any ideas why that might be?

FWIW, I'm using an SQLite database with the SQLite ODBC driver (both the most current version, at least as of a few days ago).

[Bill Harris]

I got the idea a view might work, so I tried that, but it failed, too. :-(

[nick]

I am trying to populate a document formed from a template with similar information from a database. Basically, it has a box that has a variable for the letter, then a heading under that, then it needs to print all the items from that letter. After that, a new box with the next letter should automatically be created, get the same heading, and populate itself and so forth. Do you have any idea how to do that?

[Solveig]

Hi Nick,

It's hard to say much without seeing the document. However I think you're looking at either macros, or widgets with macros built in, or some SQL logic. You might try the folks at www.oooforum.org, or Andrew Pitonyak's book.

Solveig

[Charlotte]

Maybe I'm unable to find some functions in Openoffice, but there seem to be at least two bad deficiencies in this kind of Openoffince merge compared to the MS Word "catalog merge". First, Openoffice does not ignore empty fields. This can result in a lot of empty paragraphs and some crazy spacing. Second, there's no good way to apply attributes like bold or font sizes to certain fields. I was able to work around this by placing asterisks around the text I wanted to bold and then applying "Auto Format", but this is very limited and a big pain! I believe these things are next to impossible, but maybe I just can't figure out how to do them. They're certainly not intuitive, if even possible.

[Solveig]

Hi Charlotte,

You can suppress certain fields, if they're on a line by themselves, this way. It's not automatic, definitely, but it's possible. (If you're on Linux, try glabels which does it automatically.)
http://openoffice.blogs.com/openoffice/2007/05/suppressing_bla.html

Don't worry about the hard returns, etc, if you're not in labels; just insert the section and type the databasename.tablename.filename EQ "" syntax in the Hide field.

You can make fields bold, etc. just by selecting the field placeholder and using standard formatting. When you drag in fields there is also a dropdown list in the window that lets you specify a style for the fields, too. If you drag in using tables then you can select the table column to apply formatting.

Solveig

[Charlotte]

Thanks for your reply. I see now that the suppression of fields and the application of text attributes is possible, if not easy, in these merges. Is it possible to add page numbers to the results of this kind of merge? The automatically inserted pages seem to make page numbers unusable in these merges so that OpenOffice can't produce a catalog merge with page numbers.

[adam]

http://www.batterygoshop.co.uk/hp/hstnn-db17.htm New Battery for HP M2000 Series DV1000 DV4000 ,

[annyhaiyan]

I think the good http://www.shopgogo.ca/hp/hstnn-db17.htm hp hstnn-db17 battery,and affordable prices

[yanhong]

Do You Want a hp hstnn-db17 battery?You can still get a brand new hp hstnn-db17 battery from our company.Just tell your friends to buy, Details of products are posted here: http://www.adapterlist.com/hp/hstnn-db17.htm hp hstnn-db17 battery.

[battery]

Do want to own some betteries which have more function , more economical and long life? Please visit the following Web site: http://www.battery-store.eu/laptopcomputer/asus-a32-f5-1001698.htm ASUS A32-F5 computer batteries ,it will help you find the ideal battery.

[battery]

Do want to own some betteries which have more function , more economical and long life? Please visit the following Web site: http://www.laptop-battery.org.uk/laptopbattery/dell/xps%20m1730-batteries-1001662.htm DELL XPS M1730 Laptop Battery ,it will help you find the ideal battery.

[wakesk8]

When I use the 'Insert as...Text' I have standard text along with to create my merge. When I insert it into the document it duplicates the last line, any thoughts on why this is happeneing? I'm using openoffice 3.1 for mac.

Example text to insert:
Sample Text
SampleText1

Document Output:
Sample Text
SampleText1
SampleText1