Evaluation of GnuCash Data in OpenOffice.org

Example: Summary of Monthly Expenses per Account

Please select from within an empty OpenOffice Calc document the DataPilot (Data → DataPilot → Start)

Now select "Data source registered in Openoffice.org" and press "OK".

We have to choose the data source and of course we pick our database and the Table "ALL_TRANSACTIONS".

Here comes the DataPilot.

The rectangular buttons with the fieldnames of the table "ALL_TRANSACTIONS" can be moved by "Drag & Drop" into the different areas of the layout. We do this as displayed above:

Thereafter we press "OK" and the evaluation appears - you have to wait a little once again.

Formatting

Surely Format -> Column -> Optimal Width is recommendable for all columns.
The formatting for the numbers should not be done directly unless you want unnecessarily repeat this every time you update the evaluation. Instead it is adviseable to do this via "Styles and Formatting". This is a little tricky:
1. Click into a value cell inside the evaluation.
2. Press F11 - or Format -> Styles and Formatting from the Menü.
3. The window "Styles and Formatting" should be docked to the right side of the Calc sheet. If you cannot find it, look under "Styles and Formatting" in the index of the openOffice.org help (F1).

4. Click on "DataPilot Value" in the mentioned window with your right mousebutton, i.e. call the context menue of "DataPilot value", and select "Modify".

5. Select the desired format for numbers.

6. Repeat the procedure for "DataPilot Result"

Drill-down

A doubleclick on one of the value cells will lead to the display of the involved transactions - again this is a little slow and you might have to do some formatting again. Nevertheless it is a phantastic function if you want to analyze some anomality in your expenses!

A very important extension

If you have a big data file covering many years you might find it useful to explore the following:
Define a query in your database in Ooo along the lines:
SELECT * FROM "ALL_TRANSACTIONS" WHERE "DATE_YM" >= '2008-01'
In Calc select as datasource within the DataPilot not the whole table "ALL_TRANSACTIONS" but your new query.
You will then create an evaluation that is limited to the subset you have selected by your query.
People have told me that they organized their data for many years that way creating an evaluation for every year and that it was well worth the effort (especially in combination with the drill-down function).

Other applications are obvious. Self-defined queries together with the possible variations in the layout of the DataPilot give you an incredible flexibility to evaluate whatever you want to.

Explanatory note for regular operation:

If you want to update naturally you will have to update the database before. The evaluation itself is updated with Data -> DataPilot -> Refresh. One of the cells inside of the evaluation must have the focus for this option to be executable.

Next step: Example 2: Daily Expenses of a Month