Well, this is doable. Drag the query icon from the left pane of the data source window into a spreadsheet in order to link the required record(s) as a database range. If you need more than one import range, put them on separate sheets or ensure that they are separated by blank columns and rows.
In LibreOffice you have to adjust a hidden option for the import range. Data>Define... [Import1], [More Options] "Insert or delete cells" should always be checked. In OpenOffice this is checked by default.
All details:
[Tutorial] Using registered datasources in CalcDefine a printable area on a separate sheet where you reference the imported data including the logo picture.
Calculation is just what spreadsheets are made for and Calc plays very well with database ranges. For instance, =SUM(INDEX(Import1 ; 0 ; 3)) sums column #3 of the database range named "Import1".
From a single customer record you can pick distinct values like this: =INDEX(Import2 ; 2 ; 1) [first row is the column label. row 2, column 1 is the first value]
menu:View>Print Preview helps to keep the print area within one print page as you fiddle around with the column widths and row heights. Finally you get a cleaner column layout as in your screen shot.
menu:Tools>Protect>Sheet protects the layouted print sheet when you are finished. Sheets with import ranges must not be protected.
I use a special template with prepared cell styles for my Calc reports (booleans as check marks, German currency, German date/time, text attributes for label cells etc.)
If you need more than one record set in your print area, the following macro updates all import ranges:
- Code: Select all Expand viewCollapse view
For each dbr in ThisComponent.DatabaseRanges
dbr.refresh()
Next dbr
You may use the "sheet activation" event to trigger this macro so it will update the invoice every time you activate the sheet with the print area.
You may use a non-printing push button on the sheet or a customized toolbar button as well.
Unlike "normal" reports, Calc allows you to combine reports with "power filtering" form controls as demonstrated in my above example. "Power filtering" writes filter criteria from a form to a special filter table and combines the stored criteria with report data or filtered subform data.