sub-reports

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:

Hello,

Just a bit surprised at this question. This is nothing more than grouping:

image description

You may recognize this sample from a previous question regarding the use of functions/totals. This is the same sample with a simple accumulation by group for the sub total and invoice total based on customer accumulation.

This same type of totaling is available in many other report generators under grouping and not sub reports (really a different item - report within report).

Edit 2018-05-24:

I understand you wanting a different method, and the desire for multiple source inputs and probably other items but just want to publish a clearer example of what Report Builder can do with a bit of work:

@Ratslinger, Thank you, but this isn’t what I need. I’m looking for real sub-reports.

Don’t see this as any different than your examples but OK. Try this → Subreports in Jaspersoft Studio.

@Ratslinger, btw, have you ever gotten DataVision to run?

@Ratslinger, … was typing when your jasper. comment came in. I hadn’t see that. Thank you. I’ll look into that. Appreciate it!

Yes, a very long time ago, but haven’t touched it for over a year not. Right now (and not much need for any reports) my go to is Jaspersoft Studio. Has a lot of nice extras including a browser based report server.

To help provide better answers here & in future answers, please state reason for down vote.

@Ratslinger, Your comment about jasper and DataVision were quite helpful to me! Thank you! Your answer above is clever, but real sub-reports are what I need, especially with their ability to print unrelated information on the same page. (I got called away for an hour as I was in the process of deciding how to respond here. After thinking it over your answer might be helpful to someone, so I removed my initial down-vote. Still it did not answer the questions about Sub-reports.)

DataVision and Jaspersoft Studio are quite different in polish and license. For now it looks like Jaspersoft Studio will better do what I need. But there was a lot of work put into DataVision over a 7 year time span. It has however remained unimproved for many years since. I looked at the DataVision code and briefly spoke to the author. It is copyright w/ Apache Software License, Version 2.0. Jaspersoft is open under the Eclipse Public License v1.0.

Not clear as to a few items. You state ability to print unrelated info on same page but your examples all seem to contain related information. To me unrelated would be printing an invoice with the weather report (still seems this can be done in Jasperstudio). Also, from what I read in the Jasperstudio link you can obtain data from different sources. What am I missing?

And the Eclipse License is a “GPL-Incompatible Free Software Licenses” which is a form of a “Common Public License”, and GNU says, “Unfortunately, its weak copyleft and choice of law clause make it incompatible with the GNU GPL.” So while Jaspersoft Studio is very nice, it’s not anything like LibreOffice in terms of openness of code.

@Ratslinger, First, “where hours comes from the hours table, and materials comes from the materials table”. Second, I spoke to someone at Jaspersoft yesterday. The ‘Community edition’ is limited to just ‘one data source’, obviously this could contain many tables. (Note, that LO base is also limited to one data source.) If one needs multiple data sources they need to purchase a license and those start at $1000/year minimum and this price goes up depending on volume of data used.

@Ratslinger, MS Access also had the ability to obtain data from different sources, for example some of your tables could be local, and others sucked in with ODBC from a remote server. You could then do a query joining the two, or you could produce a report with the local data at the top of the page and the remote data at the bottom of the page, as an example.
At one time I tried keeping unrelated info, e.g. Hours and Mileage, in the same table but it’s messy that way I think. So I split it up.

Thanks for the info. Did not know about the multiple source not being in the Community edition.

As for the two table item, the information is in separate tables but it is still related by the invoice. My understanding of unrelated is, among other things, ‘no connection’.

And just one more note which isn’t obvious, the second report in my answer is generated from three tables: Customers, Materials and Rental. Had a fourth (Labor) to add but didn’t see the need at the time. Using a macro, could have easily obtained data from outside source. No question all of what you ask for can be done in Base albeit with much more effort required. This is NOT to say Report Builder has ‘sub-reports’. Just that there ways to accommodate situations.

@Ratslinger, It took some work to learn how to use, and am still not sure of how to use Scriplets, but Jasper Studio Community does a very nice job I think. Thank you for the suggestion! How sweet it is to finally have a report writer again of some ability. Someday I’ll have to figure out how to call it from a LO form button, but don’t have time for that now. Still would like to have a similar tools but w/ a GNU license, but one step at a time, perhaps someday it will come. Thanks again.