LibreOffice Base - query to group and export

[Dazzer]

LO 6.3.3.2, HSQLDB, MacOS Mojave 10.14.6

Hi there

I have a requirement to produce a horizontal printout of 3 groups of people based upon data in a field (in this example Team 1, 2, 3) and also only to output data with a specific date which is located in a separate column

Please see attached base file Sample.odb

This is how I would like it to appear AFTER asking or inputting the date (see both sheets within Calc file Sample.ods

If this can be done natively within base that’s great but am happy to use a Calc file however when I tried this using Sort or Filter I couldn’t come up with a workable solution

The reason for this is that I have non technically minded personnel using the DB and cant allow them to do manual sorting/saving etc - just print the output

Thank you

[Ratslinger]

Hello,

Using Base Report Builder to create a multi column report is difficult at best. It is also not clear but can be guessed it is not always the same number of columns making this even more difficult. Using HSQLDB embedded adds to the problem as it is very old.

May be able to do something in Calc but would guess it is not simply pressing a button to get the desired results.

[Dazzer]

@Ratslinger

Hi, I kinda came to the conclusion using calc would’be more straightforward.

I have used Calc to link to the table and I linked the data but of course it is still in a single sheet layout unless I copy and paste OR link to different sheets using a Calc macroI but seems to fall down when refreshing the data - old data remains making the results innacurate…

Could you advise if I should persevere with this method or do you have another suggestion ?

Thank you for your response and help

[Ratslinger]

Hello,

Your question does not give enough details. Is it always three columns? Is there a upper limit to the number of rows? If so (or if two with a blank column or possibly four with one or two blank) then there are possibilities. In Base this can be done with a macro. It can also be done with SQL IF a different DB is used - Firebird embedded or split HSQLDB with a new DB version. Have already used a split version to produce the three column report.

Details of the possibilities is the question.

[Ratslinger]

Hello,

I will again state that HSQLDB embedded is not a good direction to take. If you stick with this make certain you take regular backups.

Have used your posted sample. Added two tables. One for the storage of the report date (requested in another question) and the second table to hold the data for the report. Have added a form which only has a button on it. This executes the macro which creates the table data and then runs the report.

Extremely simple from a user standpoint. Can even expand the form to run other reports.

Code was intentionally left simple. There are possibly changes you may want. The current construction only handles three columns but the number of rows is not limited.

Sample ----- ColumnReport.odb

[Dazzer]

Thank you Ratslinger