You may find that the database included in LibreOffice / OpenOffice delights you as much as it has me. This page tries to help you use it.
Remember that Libre Office, including its database app, "Base", is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!
There's more about Base 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 fully explained, and there are more tips, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software, 9/14-11/20.
For our discussion, we'll pretend that you have a database recording your thoughts at the end of the day, something like a diary. In our alternative universe, once a day (or less often) you will create a small record consisting of the date and a note with whatever thought strikes you as being an account of how that day was spent. It might look like....
date thought -------------- 13125 Lovely weather 13501 Fred's birthday 13508 Worked on OO tutorial 13521 Had flu 13c06 Hike in Slindon Woods 14130 Bought new Milne
While that's a pretty trivial case, I hope you will see that it captures the essentials of many much more significant database applications.
Apologies! A kind reader sent an important note 15 Nov 20... But it came at A Bad Time.
I need to re-write this tutorial! But for now, I can only say...
It may very well be A Bad Idea to use a reserved word (like "Date", whether you use an upper case letter or not... "date" or "Date") for the name of a field.
In due course, this whole essay will be re-written, using DateTho for the "Date of thought" field name. (You can, I strongly suspect, still use "Date" as an alias, thus protecting users of the database from this bit of computery arcaneness.)
So, in many places in what follows... For now, when you see "date", replace that with DateTho. (Or datetho, if your "shift-key finger is lazy, and you are sure you'll be able to decipher datetho.)
Oh. And I forgot to insert where I should have "Make a little database with a table like that, so you can do the rest of this". Sorry!
Working with dates is a good way to get a headache, so I usually go around the "build in" functions, and just use a text string in the form yymdd.
And yes, you really do have to read this section of "vegetables" if you are going to get any benefit from the "desert" to come.
That's almost self explanatory, I hope. But note that the month is done with a single character. 1-9 for January to September, a, b, or c for October, November, December. It works. It has advantages. Trust me! (And yes, OO will, by default, put 13a... after 139...)
Here, just to clear up any confusion, are the dates for the above records...
13125 Jan 25, 2013 13501 May 1, 2013 13508 May 8, 2013 13521 May 21, 2013 13c06 Dec 6, 2013 14130 Jan 30, 2014
N.B.: You must supply a leading zero for any dates in the range 1-9, as in the second and third records.
Really- this system does work well in practice. One of the prime benefits is that although the date entries are "just text", if you sort on that column, the records will be sorted into chronological order.
As it happens, I've used the "date" field for this table's primary key. Every table needs one, but the fact that the "date" field is the primary key for the table isn't important to what is to come.
I wrote the bulk of this in 2014. I used a Windows XP machine. When I started, I was using OO 3.4.1, and the sun-report-builder.
Life was not simple then!
I'm delighted to tell you that a "report builder" is now build into Libre Office (and, I would guess, Open Office too.) Ignore things you see here or in other sites from SheepdogGuides.com about installing an "add-on" report builder... be it Oracle's, Sun's, or another.
In limited experiments, the now-built-in report builder works as they did. Probably better! They were diamonds, to be sure... but still a bit rough.
In what follows, I will show you a way to select, by their dates, just a sub-set of the records in the table, and then present them in a nice report, sorted by date.
This should be a trivial task, but somehow it took me five hours the other day. Sigh. I hope you will see this tutorial in time to save you a similar "entertainment".
I would also like to mention that this tutorial will be a little different from a "typical" Sheepdog Guides Open Office tutorial... some hand-holding will be skipped.
We're going to create our report in two parts...
(I'm going to digress here and there along the way with some incidental report-building matters, too.)
I said there'd be less hand holding in this tutorial!....
Create a query (use the wizard) to extract just the records for May 2013. Call it Q-2014903... an odd-ball name, but one that is unlikely to clash with anything you already have anywhere. Include both fields in the result of the query. (Obvious in this simplistic example, I imagine.)
You don't need all of the fields in more complex cases, but you will need whatever field you want the records sorted on in the report. We are not going to ask the query to sort the records for us. (Remember that in many databases, you can't make assumptions about the sequence in which records will be "served up" when you pull them from the database. The entry sequence will not always be maintained when you start recalling records.)
While completing the Query Generator Wizard, in step 3, "Search Conditions", I used...
Match all of.... Field Condition Value Thoughts.date is greater than 13431 Thoughts.date is smaller than 13601
... which will work just fine to give me what we set as our goal: Just the records for May 2013. Remember: You and I know that "13431" is standing for April 31, 2013, but to the computer is it just a string of characters. But because of how we are coding dates, all will be well.
ARGH!!! That should have been simple!!!! I did something JUST like that yesterday.
But today, although I went through the wizard twice, it just "threw away" my search conditions. I had to go into the roughed-out query with right-click/ edit from the main Open Office project manager window, and tweak the query as follows. Notice: Two columns for the "date" field, but the "visible" property selected for only one of them.
Anyway. Sigh.... Moving on...
Jumping ahead slightly:
Once we have created the query and the report, if, at some future date, we want, say, a report of our thoughts for February 2014 (assuming some records have been entered for that period), all we do is to go into the query, edit the search conditions, and re-run the report.
I hope that is enough help for you to set up the query we need? Do write and "complain" if not... if you've already looked at my existing guides to queries.
So. We have some data in a table. We have a query which will pull a selection of records from that table.
Queries, by the way, can be seen as a way to create "scratch" tables for sundry uses... such as being the "input" to a report.
Now onward to the basic report, and then a few notes on Things You Can Do to make your report more "professional".
I hope you noticed what I said above: Save your blood pressure: Use the Oracle Report Builder Extension (Add-On).
Go to the main Open Office project manager window, select "Reports" in the left hand pane. Invoke "Use wizard to create report".
You should get something like...
In the first step, "Field Selection", select query Q-2014903, and move both of the fields ("date", "thought") to the "Fields in report" box.
Use default labels.
Don't invoke any grouping.
In step 4, "Sorting", DO NOTinstruct the report to sort by "date". I blush to tell you that the Report Generator will FAIL if you are tempted to ask it to take care of the sorting for you in this instance. We will add sorting in a moment. I don't know WHY it won't work.... but trust me, it won't. (It may be because we have applied two conditions to be met, both relating to the "date" field's contents.) (This didn't work even with OO 4.1.1 and Oracle Report Builder... as I say... probably because I have two selection criteria being applied to the field I want to sort on... but an answer is presented!... (4.1.1 accepted the "please sort" request... but, I'm pretty sure, ignored it! Doubly annoying.))
=== START digression---
The Kind Reader who wrote me 15 Nov 20 said that he thought the problem might lie in the name of the field. This is what caused me in "Messy Kludge" above to suggest naming the field DateTho. Apparently, if you do this, then you CAN sort the query result on date!! (^_^)
- - - Digression ENDS ===
The default layout will be fine, but select "portrait" orientation.
Make the report a dynamic report. Thus, the next time you run it, it will re-connect to the underlying query, which will in turn connect to the table, and pull the then current data into the report. (A static report will always display the records which were present when the report was first created... I'm not sure what that's good for, beyond near trivial uses of the report generator!)
Finish up by giving the report some name. I've used the rather basic "R-2014903" in running this process as I write the tutorial for you.
That should result in a report that "works". It may even appear to be presenting the records in chronological order. (If you want to see a "boo-boo", to prove that the order is luck not design, go back to the underlying table, add a record for, say, 13515. (Be sure to save the record.) Re-run the report. It may or may not be presented in order. In other words, it may still work, but that is a "coincidence" too. (If the date field wasn't also the primary key for the table, it could make a difference... and what I'm presenting here doesn't require that the sort field for your report be the same as it's primary key field.))... Anyway... let's put a sort into the report....
It is a pity that the basic, done-with-the-wizard sort doesn't work. But we can get what we said we were going to get, with only a few minor pains to endure.
After just a bit of simple tweaking for column width, I had...
(Remember.. the fact that the records are in order is "an accident" at this point.)
It would be nice if we could just tick a box somewhere and tell the report "sort the records".... but it isn't quite that easy. But not a lot more difficult.
The "date" and "thought" column headings are, at the moment, part of the "Header" band of the report. When we turn on sorting, "things happen".
So, before we get into how to turn on the sorting, we'll move those headings from the "special", "headings" band into the bottom of the page header band.
In a moment, we will select the two label fields (text boxes) ("date" and "thought". Once they are selected, we can just drag them to the Page Header band of the report.
As with so many things in OO, how it works actually works very well... once you understand what is expected.
To select stuff, you just drag out a box on the window. "The trick" to OO selection is to understand that unless an object is entirely within the selection box, it will not be included in the selection. Also, you need to know that the two label fields are much bigger than the text in them would require. Get the report open for editing, if it isn't already, and just click on either column heading. See the eight green drag spots? See how big the field is?
Drag out a box around ALL of the two label fields. It doesn't have to be precisely done. BITS of other objects can be included, as long as you don't include ALL of those other objects.
Once you've got a set of nine small green squares around both "date" and "thought", move the mouse pointer inside the selected items. Get to the right spot, and you get a four headed "move this" icon. Drag the headings into the "Page Header" band.
Whew! Easy to do (when you know how.) Hard to convey with text.
NOW we can turn on the sorting.
Fire up the report navigator. (Use F5 if you don't know another way.)
Right click on the "Detail" sub-section heading. Invoke "Sorting and Grouping". Use the pull down menu to put "date" in the first line of the "Groups" table. (Even though we aren't really "doing groups" at this point. It is a shared dialog, I think.)
Set the properties as follows. Most are probably the default values you saw when the dialog came up, but note the change to "Group Header" (to "not present")....
I took five hours to get that right yesterday, for the first time, on a different machine.
Today, to do it again on an older machine, which started with out-of-date OO and modules, and write most of this tutorial... another 4 hours. Sigh.
And you'll be able just to sail straight to "the answer"!
Don't despair. OO is a great package. It would be a great package if it cost what Access costs. But ANY database work is going to be more demanding that basic spreadsheet or word processing work. You CAN do it!
Now that we've invoked sorting inside the report, the "sections" or "bands" (horizontal divisions) of the report are...
So far, so obvious, up to a point.
(Before we made our sorting choice, different sections were displayed: Page Header, Header, Detail, Page Footer.)
(Reports can also have a REPORT header and footer!)
What's in the page header band will appear at the head of each page.
What's in the page footer band will appear at the foot of each page.
The detail band shows how EACH record will be handled in the course of producing the report. If the report consists of the data from, say, five records, then what's in the detail band will appear five times, with different values in the different field boxes.
Before I expand upon the above, and before you do too much work on your report's fine tuning, be sure to check what you see when you call up the Format/ Page dialog. I often find I'm having trouble making everything fit... reducing the margins gives you extra printable area to fill. This is also where you can change the report's orientation to portrait or landscape, as appropriate.
Okay... back to the main report design window.
Note that each band has a certain vertical height.
In the past, although it was fiddley, and involved starting at just the right place, you could change the height of a section, within limits, just by putting the mouse in the right place and dragging. As I mentioned above, in the course of writing this tutorial, I have had to upgrade various elements, and at the moment dragging the section borders doesn't seem to work....
But you CAN, within limits, change the size of each section.
Put enough space to be sensible at the top and bottom of each page by adjusting the height of the Page Header and Page Footer sections. You will probably want to shrink the Detail section as much as possible, to put as many records as possible on each page. But if you want a little space between lines, that's possible too... just make the Detail section a little bigger than the minimum allowed.
There are icons to shrink whatever section currently has focus, i.e. is selected. If clicking on the section names isn't "doing it" for you, you can use the Report Navigator.
I've said "within limits" a number of times.
If there's nothing in a section, you can shrink it to nothing. If there is, for instance, a label field saying "date", then shrinking of the section is constrained. You must leave enough room for the label field... but you can change the size of the label field. Using a smaller font for what's in it is something you will do from time to time, for instance.
Normally, to the right of the report you are designing, there is a column with the properties of the currently selected object. (Remember: The Report Navigator is the most uncomplicated way to select the object of your choice.)
If the column with properties isn't visible, F4 should make it appear. (F4 will also make it disappear, if it is in the way of something you want to see. (Using "View/ Zoom" is another way to get your workspace the way you want it.)
If you have one of the sections selected, one of its properties is its height. You can make a section taller by making a change to the number in the "height" property's line in the properties panel.
When a section is not "shrunk" to the minimum possible height, it is fairly straightforward to drag objects which are part of the section around on the available area.
You will be prevented from making things... or even the area set aside for them... overlap. But you can often reduce the area set aside for something.
Changing the height of the bands is fiddley, but you can do it.
A quick way to add new label fields to a section is to select an existing one, do "copy" (ctrl-C is a quick way), then do a "paste" (ctrl-V), drag the resulting copy of the first label field to where you want it, and alter the text in the label field to suit your wants.
Details of what report this is, what version of the report, what queries it draws on, etc, etc, will always repay you when you come to work with the report at some future date. Here's a version of our "Thoughts" database, with some good text in the page header. (I made the page height 15 cm to make it fit in here better.)
Notice in the above that I have modified the font of the text in the different label fields to emphasize the most important text.
Not only can you put simple text onto a report, you can also add lines. In particular, lines between records may be welcome. I'm afraid I don't know how to do, say, a line every fifth record... but I'm sure it could be done.
A little cunning is required to get a good result. If you put a line UNDER the data in "detail" section, then a line at the bottom of the Page Header section will provide a line above the first record, which will look sensible.
Making the line under the data fit up tight against it, vertically, can be tricky. Again, simply edit the number in the properties pane. Try to make the line's Y-position "0" (zero), and the system will make it the minimum acceptable value.
I've also added three vertical lines in the version of the report below. I apologize for the gaps in the vertical lines. I'm sure they aren't unavoidable, but I am getting tired of this work, and will accept them as "good enough" (for now!)
A lot of fiddley stuff... but necessary for a good report. And doing it gets easier and easier, with a little practice.
I like to know when a particular bit of hardcopy was generated. If you have, say, two lists of what books are supposed to be on a particular shelf (assuming for the moment you are working with a library inventory database), it is a Real Help to know which list is the most up to date!
Fear not! Open Office has anticipated this want!
Click in the Page Footer section. Invoke "Insert" from the main menu. Choose "Date/time". Leave both tick boxes ticked. Click OK. That will place two "formatted fields" on the report, fields which draw information from the computer at the time the report is generated. Move them to sensible places. Re-size them. Add the label field saying "Report printed:".
Watch carefully for a "little trick":
Select the label field ("Report printed") AND the two formatted fields". Look in the "properties" pane. You can set the font for all three fields together, as long as they are all selected when you go to change the font to bold, as I have done to generate....
So. There you have it. Getting everything "just so" is fiddley in places. There are a few "gotchas". But OO is more than capable of turning out this sort of oft-needed report.
If you haven't done something like this before, it probably was a bit of a struggle... but most of it, if you are like me, was in dealing with the initially frustrating system of selecting things, moving them about, changing their properties. With just a little practice, those irritations would fade. Master that "first bump" on the learning curve, and you will have a powerful tool at your fingertips.
I am sure that it would be quite simple to build something which would allow you to call up something on your screen, fill in the start date and end date you wanted your report to cover, click a button, and the editing of the query would take place automatically, behind the scenes, and the report would "magically" appear on your screen.
See my thoughts on "programming" with Open Office, if something like that sparks your imagination and ambition.
I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.
I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.
Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....
Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)
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.
Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.
AND passes...
. . . . . P a g e . . . E n d s . . . . .