# 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:

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

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

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:

more

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

( 2018-05-24 06:12:20 +0200 )edit

Don't see this as any different than your examples but OK. Try this -> Subreports in Jaspersoft Studio.

( 2018-05-24 06:19:52 +0200 )edit

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

( 2018-05-24 06:43:35 +0200 )edit

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

( 2018-05-24 06:45:32 +0200 )edit

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.

( 2018-05-24 06:49:51 +0200 )edit

( 2018-05-26 18:14:43 +0200 )edit

@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.)

( 2018-05-26 18:30:37 +0200 )edit

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.

( 2018-05-26 19:04:36 +0200 )edit

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?

( 2018-05-26 19:05:25 +0200 )edit

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.

( 2018-05-26 19:16:16 +0200 )edit