Libre Data - multi-level report

[edstevensdba]

LO 6.0.4.2 on Ubuntu 16.04 LTS.
Just started working with LO Data. Was able to quickly put together 3 tables and a form with 2 subforms to track time and materials costs of art projects. (I’m an Oracle DBA, my wife is the artist). Main table for projects, two child tables, project_time and project_materials. Form has a grid subform for time and another grid subform for materials. At this point, form is a bit crude but functional. Now I need a report. I envision a report with a group header showing data from the PROJECTS table, a sub-group showing detail and summary of time, and a second sub group showing detail and summary of materials. Something like:

Project Name:  xxxxxxx
  Time:
      Date         Time (hrs)
      6/1/18      3.5
      6/2/18      2.0
      6/3/18      0.5 
                       total time         11.0 hrs
  Materials:
     Item                     UOM             Unit Cost    Qty Used     Item Cost
     paint, oil, red         tube, 2oz     2 .35            1               2.35
     paint, oil, blue       tube, 2 oz    2.35              2              4.70
                                                              total materials     7.05

On the detail lines, all columns are from the respecitve table, except “Item Cost”, which is calculated at run-time (unit cost * qty used.)

I’ve played around a bit with both the report wizard and manual design, but have not figured out how to do this. I don’t think I need a detailed tutorial (though if there is already one available that addresses this kind of report, I’d be happy to look at it) but just some pointers on how to approach it.
Paint, red, oil

[Ratslinger]

Hello,

This is not an easy task using Report Builder. In fact it is somewhat meticulous. Report builder only accepts one query/table for input. So for starters you must create SQL to have one input. The means assigning record types (Materials, Time) and adding appropriate Customer/Project info on each record.

Once you have that, there is actually creating the report. You will need to use fields for labels and the data is to be determined by an if statement (record type). You will need to set up functions for different totals/accumulations.

Have attached a sample .odb which contains a report which will generate this:

It is not too different from what you seem to want.

Sample ----- InvoiceForumPost.odb

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[edstevensdba]

@ratslinger -
“This is not an easy task using Report Builder. In fact it is somewhat meticulous.”
What I was afraid of. Thanks for the feedback and the ideas. I’ll play around with your ideas and see what I can come up with.

[MSPhobe]

Delighted to say: This works well under embedded Firebird, too.

[Villeroy]

In your sample document you calculate function SumCost = SUM([Cost]) * 2
How come? Why is SUM([Cost]) half the sum of field “Cost”?

[Ratslinger]

@Villeroy

Why is SUM([Cost]) half the sum of field “Cost”?

.
The reason it is there is not certain. It is not used in this report and can safely be deleted. Around that time I was posting various related answers and this seems to be an item failed to be removed. For example see → How to reuse an accumulated value in a Report? - #2 by Ratslinger
.
That function was present six months before this post and even there not sure why it was present.
.
Simply an oversight.

[Villeroy]

@Ratslinger

It is not used in this report

I still have difficulties understanding that tool. This time I was convinced that the overall sum is calculated by that function.

[Ratslinger]

Overall sum has other accumulation: