In GNU/Linux, does anyone know how to produce a nice possibly multi-page report with embedded sub-reports from SQL data, (in LO Base or otherwise)?
A sub-report is one report embedded inside another. The sub-report can use unrelated tables, or it can link to the parent report’s data. Without sub-reports one is limited to only one data table or query. And all records must come from that one table or query. With sub-reports, each report, in other words the parent and each child report can have it’s own table that it draws data from. This allows a report to be generated with dis-similar data side by side.
For example, an invoice often includes hours and materials, where hours comes from the hours table, and materials comes from the materials table like this:
-- INVOICE --
Hours:
Date Work-done Start-time End-time Labor-rate Cost
-------- ------------ ---------- -------- ---------- ----------
1/1/2018 Fixed stuff 4:50 6:50 $60 $120
1/2/2018 Fixed stuff 1:00 2:00 $60 $60
==================
sub total: $180
Materials:
Date Material Cost
-------- ------------------------------------------ -----------
1/1/2018 Red parts $100
==================
sub total: $100
===============================================================
Total due: $280
Where the Hours: data is the first sub-report, and the Materials: data is the 2nd sub-report, and the main report calls (embeds or includes) these two sub-reports, and also figures out the total at the bottom. These lines print one after the other on the page(s) and in whatever font, i.e. not fixed width, but w/ columns aligned with column headers. In other words, a modern GUI produced database report from a variety of tables or queries.
NOTE! The Hours records are different from the Materials records, thought in this case they have a few similar fields, e.g. Date and Cost. In other words, they are not coming from the same table.
Here’s what I’ve already looked at:
- It seems Base reports can’t do this, or if so I don’t know how.
- I also looked at DataVision which says it can do subreports, but the source hasn’t been maintained in 10 years so I’m wary of spending time in that direction.
- And I already know about MS Access’s ability to do this, but of course that’s in Windows.
- I’ve also looked at Jasper reports, but haven’t seen how to do it with this either.
- I know I can design a base form and then print it, but it can’t handle variable numbers of records, like if I have 50 different material line items for example.
It seems like such a simple thing, so someone must have solved this before.
Thanks.
Here’s another example of what I’m looking for:
