Ask Your Question

Libre Data - multi-level report

asked 2018-06-10 21:44:03 +0200

edstevensdba gravatar image

LO 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
      Date         Time (hrs)
      6/1/18      3.5
      6/2/18      2.0
      6/3/18      0.5 
                       total time         11.0 hrs
     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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-06-10 22:32:00 +0200

Ratslinger gravatar image


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:

image description

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.

edit flag offensive delete link more


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

edstevensdba gravatar imageedstevensdba ( 2018-06-11 17:38:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-10 21:44:03 +0200

Seen: 66 times

Last updated: Jun 10 '18