Workaround: Database reports with charts in LibreOffice Calc

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Workaround: Database reports with charts in LibreOffice Calc

Post by Villeroy »

Every time I try to do some database reporting, I end up with a bug report. Charts in reports do not work in LibreOffice nor OpenOffice+ReportBuilder extension. There are many unresolved issues related to that. Switching over to LibreOffice Calc as alternative report engine, I had the honour to contribute this one: https://bugs.documentfoundation.org/sho ... ?id=129412 which will be fixed very soon since it crashes the application.

The attached spreadsheet can be opened without crashing current versions of LibreOffice (6.3.3 as of today) if you register this example database as "Gasoline". Download the database, call Tools>Options>LibreOffice Base>Databases, click the [New...] button and point to your downloaded Gasoline.odb. Topic describing Gasoline.odb: viewtopic.php?f=100&t=100402
Ironically speaking, this is the best spreadsheet ever because it does not store any data nor formulas. It pulls raw data from an external source, transforms the normalized record set into a dynamic cross table layout (a pivot table) and plots this cross table into a dynamic chart, a pivot chart.
It shares the same set of filter buttons with its pivot table. It does not plot any totals nor subtotals which may be included in a data pilot.

How I created the pivot table
Data>Pivot>Create...
Create pivot from registered data source with database "Gasoline", source type "Query" and source "qReport"
Drag "Vehicle" to column fields, "Date" to row fields and "Consumption" to data fields. Double-click the data field and change function Sum to Average since you don't want the sum of consumption rates for one day. Use "Range" instead of "Consumption" if you want the ration distance/volume as in Miles/Gallon, however the example data in my database contain km and Liters in European prices.

How I created the pivot chart with 2 clicks
With the cell cursor in the pivot table I called the chart wizard and clicked [OK].
The resulting chart treats column fields as data series (2 vehicles "Kawasaki" and "Porsche"), row fields as x-values (the dates) and data fields as as y-values (average consumption rates).
When the database has changed, all you need to do is right-click>Refresh the pivot table. Any new vehicle will expand the table by one column and the chart by one data series. Any new gasoline consumption will expand the pivot by one row for the date and the chart by one new data column plotting the consumption value on the y-axis.
Attachments
Gasoline.ods
Database report with chart on a spreadsheet (LibreOffice only)
(21.74 KiB) Downloaded 393 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply