[Writer] Stand-Alone Database Reports
Posted: Sat Jan 09, 2016 9:54 pm
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.
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
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.
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