Multiple groupings in report

English
, , , ,
#1

Base 7.0.4.2 (x64) Firebird DB Windows 10
Following on from my earlier question about a parameter query not working, I have now got a working query which extracts all the data I need for my report. (image1)

query
query895×193 6.23 KB

I have created a report based on this query with a Page Header containing the main data (Client) and three groupings:

The group settings are shown in image3. However, it appears I have reached the limit of my understanding of how grouping works in the report generator, since the resulting report I get fails to group data sharing the same group criteria (see attached document). My test data for this is C_Client_ID = 5, which has 2 Dependants, 2 Accomodations and 1 Support Organisation. What am I doing wrong? Can anyone help?
R_ClientProfile_5.odt

image description

#2

Hello,

While getting ready to put together a sample, it occurred to me what you problem most likely is. With your query, you have only one group - “ClientPersonal”. Everything else is related to that.

So how do you put together a report based upon your example? You need more information to group on. While your “ClientPersonal” information is present in every record, the other table information needs unique record id and attach to the main record. These would each be separate records:

"ClientPersonal" info + "RecType1" + "ClientDependant1" info
"ClientPersonal" info + "RecType1" + "ClientDependant2" info
"ClientPersonal" info + "RecType2" + "ClientAccom1" info
"ClientPersonal" info + "RecType2" + "ClientAccom2" info
"ClientPersonal" info + "RecType3" + "ClientSupportOrg" info

Then based upon the record type, you print headings and detail. Even with this explanation it is probably not clear. Will take some meticulous planning and layout work. Your query will need to change and records are not easy to accommodate common fields.

To give some further direction/help, there is a vaguely similar example in this post → Libre Data - multi-level report. This report only has two record types beside the master. Yours needs a third record type.

Edit 2021-02-18:

To provide a somewhat better example, did q quick & dirty mock of a report. Had to use a View because the SQL needed required a pass through and then you cannot use the parameter. So most of SQL is in View and query selects from that and uses the parameter.

Sample ----- ClientReport.odb

There is one account set with a record id = 1

#3

Hey, Ratslinger, looks like you’ve cracked it yet again! I had looked at your ‘Libre Data - multi-level report’ before I posted yesterday, but must confess I didn’t really understand what was going on. Your example database makes it clear what I have to do, so I’ll get on with it and report back when I’ve got somewhere. Many thanks!

#4

@bbater,

Wish you the best. Still, expect further difficulties. Putting together the SQL will be difficult. When dealing with Unions all records are the same. That’s the tough part. Once done it’s what appears and when in the report - no picnic there either.

My advice is to take it one step at a time. Do not try to get the entire report done in one pass. My sample shows how that first step can be fruitful. The multi-level report in the other post took many steps to get into the current state but gave a lot of insight on how to get things done in Report Builder.

#5

Thanks for your advice above. Tried creating first SELECT in your ReportView (omitting following UNIONs) and saved it as a View as first step. I then planned to add the next Select but I had no ‘Edit in SQL View’ option on Edit menu or Table toolbar to add further code. Added ‘Edit in SQL View’ to menu and toolbar via ‘Customize’, but although these options have been added, they are greyed-out. Any idea why? Added to which the Report Builder freezes every few minutes and needs recovering, so I have to save the report and the odb after every change, and it’s taking a long while to get anywhere. Had enough for tonight, back on it tomorrow.

#6

@bbater,

There is an open bug on this → tdf#126960

You have a few choices. You can delete an existing View and create a new View when changing code. Another option is to just use as a query. It is my preference often to use a View when reports are involved. Retrieval of data is quicker. Just tested the SQL in a Firebird Base document and had no problem. If changing to a query you must still keep it separate form the other query. One requires Run SQL directly to be on and the one with a parameter cannot have it on.

A third option may be to test as a query and when done make it a View.

Have no answer to your Report Builder freezing. Have no such problems in Ubuntu 20.04 with LO v7.0.4.2. Also, for the heck of it, tested it with Alpha v7.2.0.0 and with only a couple of runs all seems OK.

#7

Ratslinger, sorry but Report Builder (RB) is causing me so many problems that I’ve had to abandon the route you were helping me take. RB regularly freezes/crashes - 7-8 times an hour. It always needs to be recovered, which sometimes goes as expected but other times just repeatedly flashes an alert 2-3 times a second saying “Synchronizing repository for bundled extensions” which can only be stopped by killing the process in Windows Task Manager. There are serious problems with the Datafield Properties dialogue too. Trying to select a DataField Type to be a function like TODAY() causes the whole report UI to go black. Even more annoying is trying to enter a datafield name or function manually; after every keystroke, the cursor returns to the start of the field, so you end up actually typing right-to-left!

More to follow…

#8

So I’ve decided to cut my losses and use what I had prepared before. There is no grouping, the report repeats the set of three data groups - Dependants, Accommodation and SupportOrg - as many times as there are any of them. I.e., if there is no Dependant and one Accommodation but two SupportOrgs, I get:

Dependant
(blank)
Accommodation
data1
SupportOrg
data1
-----
Dependant
(blank)
Accommodation
data1
SupportOrg
data2

Not ideal, but all the data is there, so my client will have to put up with it. There’s a second report with further data from different tables which needs to be printed at the same time, so I’ve written a macro which prints both reports one after the other. All I have to do now is to import all these new items into the live database!

So, sorry the outcome wasn’t as intended, but thanks for all your help anyway. I certainly learnt more about Left Joins and other things and hope you may feel you didn’t entirely waste your time either.

Regards,

Bob

#9

@bbater,

Unfortunate to hear of all your issues. Just retried some of your items with Firebird DB and Base 7.0.4.2 (x64). Only difference is I am on Ubuntu 20.04 (may try windows later or tomorrow). Have no problems with Inserting a field for Today() or entry a data field function. Crashing is not happening either. Possibly you have an issue with your User Profile? See → LibreOffice user profile

Edit:

Just moved to Win 10 (basically your set-up) out of curiosity (don’t like Win personally). There is a problem with entering in the Data Field. This should be reported as a bug. As a work around I used Notepad to write the wanted line & then did a copy/paste without a problem.

No problem with using Today() or crashing.

#10

@bbater,

While answering another post I realized another possible difference in my Windows install. Not using Oracle Java. Using:

AdoptOpenJDK 11.0.8
https://adoptopenjdk.net/
#11

OK, followed link advice to start in Safe Mode. Used the RB for an hour or so without any crashes or freezes. However, manual entry of function still writes R-to-L although Function Builder works OK. Also, my function below on a Boolean field shows ‘NO’ whether the field contains 1 or 0.

IF([C_Domestic];"YES";"NO")

So if my User Profile is causing the crashes, any idea what I can do about it? Also, I’m using Oracle jre1.8.0_281. Do you think it would help if I change to the one you are using? Seems latest is jdk-11.0.10+9.

#12

@bbater,

manual entry of function still writes R-to-L

Yes, that was noted as a bug in my last comment.

Will need to further test the Boolean condition. Current test shows working on Ubuntu as you show.

As for the User Profile, this is not an area I am comfortable with. In my past experience, have renamed the old and created a new one (often now I have backup copies). Since this holds all settings, all must be re-established and things such as extensions reloaded. The link given in my last comment has a list of all file contents for the user profile. It is my understanding you may be able to use some to recover but again not familiar enough with this. You may want to search the Ask site for help in this regard - possibly ask a new question.

Not certain changing Java will help. As an FYI have been using Open JDK on Linux for some time.

#13

Again moved to Windows and re-tested IF and this:

if([myBOOL];"Yes";"No")

Was no problem. Produced correct results in Windows 10 and Ubuntu 10.04 - each using LO v7.0.4.2

#14

My client has accepted the reports including the repeated data, so I have updated his live database. BTW, I still can’t get the Boolean field to read “Yes” or “No” using the function above. If True it’s blank, but if not, it reads ‘FALSE’. I’ll not be working on the database further unless they come back and request further features, which I doubt. So, many thanks for all your help. I’d not have got here without it.

#15

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.