[Writer] Stand-Alone Database Reports

[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

[RPG]

Hello

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.

Romke

[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

[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.

[Villeroy]

How to pimp up your own Writer template

Install Python Macro
I attached an installer to this posting which installs the Python code to your user profile. After installation you find the pair of macros to fill and clear a specified Writer table under Tools>Macros>Organize>Python... "My Macros">pyWriter>DBReport or Tools>Macros>Run... "My Macros">pyWriter>DBReport

Setup Your Template
If you want the same thing in your own template as demonstrated by my template:
1) Open your template for editing.
2) Create the first two rows of your own table. One header and the first data row. Fill the data row with some dummy data and format them any way you want. My macro tries to import numbers as numbers, dates as dates, times as times etc without touching any formatting attributes in the table cells.
3) Call File>Properties... tab:Custom and add the user-defined properties as documented in my template.
• RPT_DataSource is the name of your registered database (e.g. “Bibliography”)
• RPT_Source the name of a table, the name of a query or a SELECT statement.
• RPT_SourceType is number 0, 1 or 2 depending on the source being a table name, a query name or a SELECT statement.
• RPT_TextTable is the name of your text table as shown in the navigator (F5)
• RPT_User and RPT_Password are optional to provide the log-in credentials for databases that require log-in.

Add Some User Interface
Without any user interface, you can call the macros via Tools>Macros>Run... or via Tools>Macros>Organize>Python...
I added a custom toolbar with 3 buttons to my template. If you want the same:
4) Activate your template file and call Tools>Customize... tab:Toolbars.
5) Switch the scope from "LibreOffice Writer" to the name of your template. This stores the toolbar in your template so it appears only in your template and derived documents. Create a new toolbar with 2 toolbar buttons for the 2 macros and if you like, add command "Properties" of category "Document" which calls the built-in properties dialog of step 3.

Alternatively, you may add menu entries, shortcuts and/or you can attach push buttons to your template and make them unprintable, so they appear on screen only. Bind the execute event of one button to the macro clearing the table. Bind the execute event of the other button to the macro filling the table.