AUTHOR'S MAIN SITE  /  TABLE OF CONTENTS for Open Office database tutorials.
Delicious.Com Bookmark this on Delicious    StumbleUpon.Com Recommend to StumbleUpon

Open Office Database Tutorials

Introduction to creating reports

You may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.

Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.

There's more about ooBase in the main index to this material.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software , 2/06-7/15.



Creating reports with Open Office's database, ooBase _______________

Once known as the Sun Report Builder

Update for this page, 7/15....

Ah, "When I was a boy...."

This page dates back to 2008. Open Office was a different creature back then! We've come a long way, baby!

I think that what was once the "Sun Report Builder", and was an add-on, and then became known as the Oracle Report Builder is now the "standard", "built in", "comes with" report gernerator. Hurrah! It is good, and what we had before was... umm... "a start".

So! I don't think you'll need my "install" instructions. But, PLEASE??: Get in touch, if you find that is wrong, and you are working with a "mainstream" version (post Version 3) of Open Office or Libre Office?

I have hived off the "install" Open Office Report Generator information, just in case I am wrong! The rest of this page has not been heavily edited for a few years at 7/15, but even so may help you with puzzles. I hope so! Or may contain outdated material... please "complain"!

(End of prefatory notes added 7/15)

Report Builder from scratch_________

I re-wrote this in November 2008 when I installed Report Builder into ooBase version 3.0.0 running on a Windows XP (SP3) machine.

While what you get in different environments should be similar, what follows has been re-written again in June 09 when I updated an OO ver 2.2 installation on a Windows XP (SP3) machine, and installed the then-current Report Builder version 1.1.0 into the resulting ooBase version 3.1.0.

I took a chance, and trusted the Open Office update process.... and had no regrets. All I did was download the recommended "Install OO on your machine, with Java RTE included" file, ran that from my downloads folder.... and Open Office behaved! Cleared away the old installation just fine. (I had to remove a few shortcuts I'd set up by hand, in eccentric places.) Obviously, I hope, I had closed everything I could before starting the above. I didn't turn off my internet security package during the install, though... but all went well anyway. The system didn't even need a re-boot.

I installed the optional Windows Explorer extension, and now thumbnails give previews... not only of the obvious files (OO files), but also of JPEGs, etc. And folders have thumbnails of four of their contents on them.

A first Report Builder session_____________

Having "caught your rabbit....."

1) Start up ooBase. Report Builder is an integral part of ooBase, not a separate application.

2) Open an existing database, or create a little "test" database. A simple "Serial number (Unique, auto increment integer, and primary key), name, rank" personnel database would be fine. It doesn't need to be "registered", but it doesn't much matter if you do register things, so I tend to register things, "just in case". (There are times when it matters, of course. But this isn't one of them.) If you create a database for testing your Report Builder, put five or so records in it's table.

3) In the main project management window of ooBase, select "Reports" in the left hand pane, and then "Create Report in Design View". (alternatively, you can use the main menu bar. The fourth menu entry is "Insert". Click that, and one of your choices is "Report". Click that, and you will launch the Report Builder. If "insert" seems odd, here's why it isn't: You're "inserting" something new into the collection of tables, forms, queries, reports, etc which make up "the database". Don't fall into the trap of thinking that your mere table is "the database." It is part of the database. (An important part, admittedly!)

(By the way... you can still get to the good old, limited old "Report Wizard" if you want to... you get that by selecting (left column) "Reports" in ooBase's main project management window, and then clicking on the "Use Wizard to Create Report" in the "Tasks" pane....)

4) Whatever method you use to start it, the Report Builder design window is PRETTY!

You can check if you have the Report Builder report design window open by looking at its title bar. It will say "{database's name} {name (Report1, until you give it a better name during a save}- OpenOffice.org Base: Sun Report Builder". The Report Builder report design window has a big pane with three wide rows: Page Header, Detail, and Page Footer. You should also have a columnar pane at the right with information about the currently selected object. When you launch Report Builder, this pane will probably have two tabs: General and Data. If you don't have this pane, click the "View" entry in the Report Designer menu, and click on "Properties" to turn on the Properties pane.

Before OO 3.1 + SRB 1.1, at this stage you would have what I'm going to describe in a moment. From 3.1 / 1.1, your report design session will open with a field selection window already open... and you can skip down to the heading "From OO 3.1 / SRB 1.1"

5) In the Properties pane, you are probably on a tab called "Data", with "content type", "content", etc. properties. To be sure you are in the right place, click on the General tab, and be sure that the properties you're looking at are for the object named "Report". If you are, go back to the "Data" tab.

If you aren't looking at the report's properties, click on the gray area below the "page footer" band over in the pane at the left. This should take you to the "Data" tab of the report's properties.

6) Leave the content type set to "table". (Reports can also be generated from queries and SQL commands, but those are stories for another day!)

Click on the "v" to the right of the "Content" box, and you should be presented with a list of the tables in your project. Click on one of them.

Now click in the "Detail" band of the design pane. A new window should pop up with a list of the fields from the table you specified as the source of the report's data. If it doesn't, you may have fluffed the specification of the report's data content type or source. But the window with the fields sometimes goes away when you don't want it to. It can be recovered with "View | Add Field" from Report Builder's main menu. (You aren't adding a field when you click that... you are reviving the "Add Field" window.)


From OO 3.1 / SRB 1.1

In 3.1 / 1.1, when your report design session opens, you should see a window listing the fields of your table.

(And now, thankfully, the narrative returns to one stream for whatever version you have...)

7) Either double click on one of the field names, or drag it from the "Add field" window onto the right hand part of the detail band. (That part of the band will probably be covered with a grid of dots.) This will create a label and a rectangle denoting where data from the field will appear on the report. Initially, you can drag them, together, around the part of the detail band. The two things you created are currently BOTH selected. It is easy to get just one of them selected, and when it is, only that moves when you do a drag. Don't worry about this much yet. Just, somehow, get your newly placed objects someplace sensible.

Push the boat out! Drag another of the table's fields onto the detail pane. It doesn't matter much how things are laid out at this stage. If you get flashes of red, it is just Report Builder telling you "can't put that there."

8) This would be a good time to save what you have so far. "File | Save"

9) Run the report! There's an button for it on the toolbar (small horizontal green arrow lying over a "page"), or use "Edit | Execute Report", or the keyboard shortcut: ctrl-E.

Don't expect and "instant" result... but before too long you should get a report! (It will be not long at all with a small database on a "modern" machine, but maybe long enough to worry you if the database is large or the machine slow.)

You should get something vaguely reasonable... a document with the values of the records from your table. Layout is probably a mess at this stage, but do not dismay! Once you learn to "drive" it, Report Builder can create many useful documents.

Close the report which just appeared. You should find that the Report Designer is under it, ready for your "tweaking" of the report.

So! This tutorial was supposed to help you set up Report Builder, and be sure it is working. Thus we should be, at this stage, done. However, an earlier draft of this tutorial had more, which follows.

Another test of your Report Builder installation__________________

You can probably just use a database which you already have, but I'm going to give you details of one that will EXACTLY work in the demo of Report Designer that I'm going to give you.

I've used slightly different techniques in what follows, compared to how I did the report above. Don't be thrown by that. Once you know two or more ways of doing something, you are empowered to choose the one you like best!

The database... call it FDB1RBintro.odb... will hold a personal telephone number directory. For the following, it needs just one table: PhoneNums with the following six fields:

1) Create the tableor simply download the whole database, with data and report, from my site, and fill it with some values. It doesn't need to be registered, but it must be in a folder where you have permission to write to the files. Be sure to have records for people from at least two countries, in order to see a feature demonstrated later. The country code for the US is 1, and for the UK it is 44

By the way... the database will run without being registered on your system. It must, however, be in a folder where you have permission to write files.

2) Fire up the Report Builder. ("Reports" pane / "Create in Design View", or, from menu, "Insert | Report".)

In OooBase 3.1 + SRB 1.1, you will immediately be offered the chance to put fields on the form, and you will probably use this extensively, in the long run. For now, just click on the little red x of the Field Selector window to make it go away. And don't worry: When you work with more complicated databases, there is a way to get fields from more than one table into a report.

3) Let's "walk" before we run.... Find (by hovering mouse over icons) the LABEL FIELD icon in the toolbar... third from left. It has "ABC" on a plain button. You DON'T want the one next door, "ABC" in a box, "Text Box" control. Click on the "Label Field" button, and it should "go down".

Move your mouse pointer into the largish area of dots- in- a- grid, a little to the right of the "Page Header" Row. You want it in the area under the "...1....2....3....." ruler. Hold the mouse button down, move the mouse a little down and right, release mouse button. In other words "drag out" a control onto the Page Header area of the report designer.

As soon as (or sooner) you release the mouse button, on the right hand edge of the Report Builder window, you will see the properties of the control you've just added to the report. In this case, there is just one tab, "General", and under it the properties Name, Label, Position X, etc, etc.

If you don't see that, you've probably somehow de-selected the control. Or turned off the view of the properties pane. Just click within the box you dragged out to re-select it. The box, so far, has, quite small, the word "Label" in it.

In the properties, change the label to "My First Report". What's on the WISIWIG part of the designer won't change until you leave the edit box of the label property.

Click on the "..." button to the right of the font name. Change the font size to 15.

Do "File | Save". Name it "MyFirstReport-tkb" if your initials are "tkb". If not, change that part to your initials. (This to distinguish things that are BY you from the multitude of "My Documents", "My Pictures", etc set up for you by nice Mr Gates.)

Have a look in the reports list of the ooBase project manager window. (Don't close the Report Builder window to do that. Just slide it aside, if necessary.) You will see the report you just created listed there, just like, and eventually, mixed among, any reports created with ooBase's built in Report Wizard. At a future time, right click any of them, select "Edit", and they come up in whichever tool created them.

4) Don't bother trying to run your report yet. It should give you something, I would think, but it didn't when I tried it under my old setup. (It did give me something sensible with ooBase 3.1 + SRB 1.1.)

5) Click on the Report Navigator icon... top row, near middle, small red line (part of a compass). In the window that comes up, click on the top entry, "Report". Notice that the properties pane (right hand side of window) now has two tabs. Go to the data tab. Leave Content Type showing "Table". Set Content to PhoneNums, using the lookup control if necessary.

Now click in the "Detail" band of the design pane.

6) When you set the Content to PhoneNums and clicked in the "Detail" band, another small window will have popped up titled "Add field...", and listing the fields of the PhoneNums table. Drag Sname and Number into the "Detail" part of the report design. You'll find that each gives rise to a label. which will have "Sname" or "Number" in it, and a data field which will be a rectangle.

You can move these around and resize them. You may need to use the pane's scroll (horizontal) bar to find everything you've put on the report design. Drag the right hand side of the two labels left until the label control is no bigger than it needs to be.

Move all four elements (2 labels, 2 fields) into a single line (row) near the top of the "Details" area. If you drag over an area larger than the furthest extent of all four elements, you can select all of them, and then drag them together, without messing up existing relative positionings.

Now drag the boundary between the "detail" and the "page footer" section up, so that the "detail" section isn't higher than it needs to be.

Again: save what you've done.

7) There is a "Run Report" (or "Execute Report") button on the designer's toolbar, which I suggest you use while developing a report. Once the design of the report is finished, you can run it by double clicking on its name.... but, before OO 3.1, close the designer first, if launching the report this alternate way. With the "Run Report" button you get a nice (if less than informative!) error message when there are problems in the report's design. Launch a faulty report the other way, and you may merely get an hour glass briefly, but then nothing. The main ooBase project management window will still be sitting there, smug and "working",... but no report. When this happened to me, I could still open the report for editing.

(Aside: Back in ooBase 2.x, while you would GET a "result" if you didn't bother to close the report design window, first, you also sometimes found that ooBase won't shut down nicely if you failed to close the report design window before the next step. Or something else may have given rise to two Ctrl/Alt/Del events for me... I'm not sure. When I DID close the design window before running the report, ooBase "behaved". I hope that this rough edge no longer exists, now that we have ooBase ver 3.x... but if it does, you know how to avoid it! End of Aside.)

That's it! You've seen Report Builder in action, I hope. I was delighted by how easily it installed, how well a "third party extension" integrated with the main ooBase code.

"Oliver" time.... more......

Now let's "get clever".... We're going to group the output by the country the person's phone is in.

1) Close the report.

2) Right-click on the report's name and click "Edit". The report design tool should re-open.

3) Click on the "Add Field" tool button... just to the left of the Report Navigator, 3x4 grid with green spot in center.

Drag Country onto the Detail part of the design.

4) Right click within the Detail area, and select "Sorting and Grouping".

Use the lookup pulldown to put Country on the first line, and close the sorting and grouping window. Your design should have gained a "Country Header" row, or to give it a more usual name in this context, band.

5) Drag the country label and field rectangle up into the "Country header" band.

Tidy up spacings, control size, etc, as before.

6) Save report design and close designer.

7) Double click on the report's name in the ooBase main project management window.

Ta! Da! (I hope!!)

Further odds and ends....

I even got lucky... my first installation was of version 1.0.1. When 1.0.2 came along the next morning (typical!), I tried the from- within- ooBase update- the- extension mechanism, and it seemed like it "just worked" :-)

I'm not going to explain it here and now... but I even got the report to add up the area codes present in each group. While the total of the area codes isn't very useful (!), it does show that more useful things CAN be done. A "trick" to look out for: If you try something similar, and you're still on Report Builder 1.0.1, be sure to untick the "pre-calc" or somesuch tickbox on the Data tab for the properties of your sub-total control. (P.S.: Brave New World is with us! I wrote the first draft of this last night, upgraded to 1.0.2 this morning, and now tickboxes that I'm SURE were there 24 hours ago have vanished!!)

It's not Report Builder's Fault!____________

If you haven't worked with something similar previously, you may think that Report Builder is "dumb". Rather like, at first, people working with relational databases would rather work directly with the tables, instead of creating forms through which to access the tables.

I have to tell you that the way Report Builder goes about creating a report is very similar to other products I've used. The problem isn't with Report Builder, it's with the way these things are "always" done. I actually quite like the Report Builder interface... it is easier to use than some I've used before. "Easier" being carefully chosen from the subset of relative terms.

It took me a long time to understand something. Don't come to a computer too firmly wedded to what you want to do. Try to come to it with an attitude of "I need to get a result. How we get there isn't too important to me. I'll try to learn how the programs that exist get people to where they want to be, and not be too insistent on the route of my choice."

Enough preaching from me! You go and enjoy Report Builder!





Fixing a "dead" report________________

At one point, after fiddling with grouping, I was left with a report I could open for editing, but couldn't run. I'd tried putting some things in. They didn't work, so I took them out again... I thought. I think I left scraps behind.

With the report open in the designer, I clicked on the Report Navigator button (little bit of red in it). One of the "foo Functions" entries had a "+" in front of it. When I clicked on that, a list of stored functions appeared. I right-clicked on each, selected delete. Once these were cleaned out, my report worked again.



Editorial Philosophy

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.




Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .