I’m trying to insert the results of several queries into a single Writer document. So far I’ve got a database connection set up in Base, written a few queries and can insert their results in Writer by pressing F4, navigating to the correct query and dragging the query name into the document. I then get a dialog that lets me insert the results as a table in the Writer document. So far so good.
The problem is: It doesn’t seem possible to refresh this data. This is a problem, because I frequently need to create this report with up-to-date information.
Is it possible in some way to refresh this data, or is there another way I can get the results of a query into a Writer document with the ability to refresh?
Other things I’ve tried and why they’re not satisfactory:
-
Insert data as a form table control. I can get results of a query in the document using forms (
Form Controls toolbar -> Design mode ON -> Table Control). These refresh automatically, but the form control isn’t printable or exportable to PDF. -
Load data in Calc and paste into Writer using DDE linking. This method involves the same process as with Writer. Open datasources using
F4, drag and drop the query into the document. Calc does allow me to refresh the data by selecting the output and then selectingData -> Refresh range. So far, so good. I can then insert the data into the Writer document by copy-pasting the range and pasting as a DDE link. If I now modify the data in Calc, the Writer document updates automatically. I can even get charts to update automatically.
The problem with this is that if new data is inserted into the database, the selection range in Writer does not update. I.e. if Calc has 20 rows, and I insert a new row, then Writer will always keep displaying 20 rows. There does not seem to be a solution around this problem. -
Insert OLE object: Inserting an OLE link to a Calc spreadsheet that has the output of the query (and which can be refresh; see previous solution) works, but it is not possible to style the table output. Refresh the data range removes all formatting.
Edit: I’ve found that it’s possible to style the table output by styling it in Calc, however the OLE object as seen in Writer gets a weird height/width aspect ratio if more data is inserted in the Calc sheet. - Oracle Report Designer: A plugin for Office that simply doesn’t work. It crashes all versions of Open/LibreOffice I’ve tried.
- Pentaho and other solutions: These seem incredibly overkill and complex for such a simple task. I would be okay if it’s suggested as a solution, but then please point me to some decent documentation that shows how to accomplish what I’m trying.
So can anybody shine a light on this?
I’m okay with basically any kind of “can be updated automatically” solution, even if it’s generating .csv or HTML files using external scripts. I’m a programmer, so macro solutions are also a possiblity (I just have no idea how viable a possibility the are).
You have my thanks in advance for any input!