Page 1 of 1

[Writer] Stand-Alone Database Reports

PostPosted: Sat Jan 09, 2016 9:54 pm
by Villeroy
The attachment is a Writer template with some explanations on the first page and an example report on the second page. The example report is based on the "Bibliography" database that is shipped with the office suite. The template has been tested with various database queries running under LO 4.4.6 and AOO 4.1.2. It contains a little bit of embedded Python code and a toolbar button with 3 buttons to fill/refresh, clear and configure your report.
Currently, the report table consists of one flat Writer table similar to the old-style reports that are embedded in a Base document when there is no report builder installed. This one can be set up manually, seems to work equally well with tables, direct SQL queries and parsed SQL queries but it does not offer any grouping yet.
You set up a Writer table with 2 formatted rows. The first row of column headers remains untouched, the second row may contain some dummy data and formatting attributes for the data rows. It will be overwritten with the first record and serves as a format template for all subsequent data rows.
A toolbar attached to this template has a button to fill the table with the current data set, another button to clear all rows except the first two rows and a third button calls the built-in dialog for the file properties where we set up the everything on tab "Custom Properties".
Glitch: The clearing macro does not clear the second row (first data row) because of bad table implementation in LibreOffice which loses all number formatting. Format and content are not separated in LibreOffice text tables.

Example table with toolbar "Database Report" and configuration via "Custom Properties"

RPT_DataSource is the name of the registered database.
RPT_Source is the name of the table or query or an SQL statement.
RPT_SourceType is 0 in case of a table or 1 in case of a query or 2 in case of an SQL statement.
RPT_Report table is the name of the text table receiving the data.
RPT_User and RPT_Password are optional.

This is another step towards database access without an "integration" that gives us only disadvantages.
You can easily extract the embedded HSQL database out of a Base container. A little Python script may be considered helpful if you know how to run Python scripts on your system: Platform independent script to extract embedded databases
Then you can reconnect your Base document to the extracted HSQLDB using my Basic macros bundled in the FreeHSQLDB v.0.3 extension.
The Basic extension includes a supplentary macro to extract all embedded input forms into a folder hierarchy outside the Base document. Another Basic module implements an auto-installer which lets you build self-installing database packages with your database, your Base documents and input forms in stand-alone documents.
This report template lets you distribute stand-alone Writer reports as well. Calc documents with import ranges and pivot tables also serve as stand-alone reporting tools when you want charts, calculations and some filtering and sorting directly on the report sheet.

My main goal is a report template which is able to fill out dependent tables based on mail merge fields. This way we could have a powerful invoicing engine based on serial letters with custom details in mail merge fields and matching sales in text tables.
2016-01-10: improved date/time conversion

Re: [Writer, Call For Testing] Stand-Alone Database Reports

PostPosted: Sat Jan 09, 2016 11:43 pm
by RPG

For me was it working.
I did use a query in an other database and I did get a table in a writer document.

I did made the report table with LibreOffice 4.5.2 and could use the same document for OpenOffice 4.1.2. The OS was always openSUSE 13.2

For other people who do test it: use a small table. If you use a table with a lot of records then: take the time.


Re: [Writer, Call For Testing] Stand-Alone Database Reports

PostPosted: Sun Jan 10, 2016 12:59 am
by Villeroy
Thank you for testing. Indeed, it is a bit slower than the embedded reports because I can not find a way to dump data sets into a Writer table. I have to use a cell-by-cell approach with a lot of data conversion, particularly when date/time values are involved.
On my old laptop 8,000 cells take 150 seconds. I tested dBase, embedded HSQL, HSQL2 and a spreadsheet.
A minor problem I did not solve yet: If you really need to report the results of a direct SQL query, you can not define the directly in the Writer document's custom properties. You have to store your SELECT statement as a view (RPT_SourceType = 0 like a table) or as a direct SQL query (RPT_SourceType =1). RPT_SourceType = 2 implies a parsed Query.

 Edit: Found a bug when converting time values coming from a csv file. It has HH:MM:SS without fractions of seconds. My conversion routine assumes that all Base times include fractions of seconds. Sigh. 

2016-01-10: improved date/time conversion

Re: [Writer] Stand-Alone Database Reports

PostPosted: Tue Feb 26, 2019 3:00 am
by Rob_Davidson
Thank you for this.

I used this solution for a query. At first I just deleted the sample table and created a new table, following the instructions. Repeatedly, the process hung and I had to Force/Quit (OSX). Once I deleted all the instruction text, it worked very well. Not sure why the text on the page inhibited the process.