[SOLVED] Master Detail Report with multiple detail records

[robleyd]

I'm building an Ancestry database. I have a query that pulls data from 4 different tables, person, sibling, spouse, children. The query returns info about the person, then a few details from each of the other three tables. I'm trying to build a report, using the Oracle reports extension (ORB) and I'm on version 4.15 of AOO, running on Windows 10. I have two problems.
First, I want to build a master detail report that lists the person info and then displays the rows for the spouses, then siblings, then children. If I put all this info in the detail section, then the report outputs, spouse row, sibling row, child row and then it repeats. I want to group all the spouse data together, then the sibling rows and finally the children. These are not nested sub-groups, they are independent of each other, beside the fact that they all relate to the same person. I tried putting the data from each of the sub-groups, for lack of a better word, in different regions of the report. I made a version where I broke on person_id and included both the header and footer sections on either side of the detail section. It almost worked, but it didn't, the footer section with detail info only iterated the one time with the person_id and missed additional child records.

It's like I need multiple detail sections, or is there a way to handle it by formatting the individual cells within the detail section?

My second problem is that ever since I added the ORB to my AOO. I keep crashing and corrupting my database file. One second, I'm working away and the next, then entire application has closed and is gone from view. When you try to relaunch the database file, AOO doesn't know how to open it. Anybody else having this problem?

Thanks in Advance,
Barry

[LilZebra]

There is a freeware programme called GRAMPS that'll do that easily for you.
Other software similar has a special file format that is usually compatible across different genealogy software.

https://en.wikipedia.org/wiki/Gramps

https://github.com/gramps-project/gramps

Try that first.

[lewisbarry92]

Thanks for the info LilZebra!! I'll definitely look into it. I do, however, want to make sure that I'm not missing something in the ORB, or another extension, that I could use to build my report in the AOO environment.

Thanks Again!!


Barry

[UnklDonald418]

I keep crashing and corrupting my database file. One second, I'm working away and the next, then entire application has closed and is gone from view. When you try to relaunch the database file, AOO doesn't know how to open it. Anybody else having this problem?

Yes, that is an inherent problem when using the Embedded database model supplied with the distributed version of BASE. A Base obd file is actually a zip archive containing all the tables, queries, forms and reports in your database. When OO crashes the zip fails and thus the corrupt file. Also, the Embedded database is locked into the HSQLDB engine version 1.8 which is about 10 year old. The solution is to migrate to a "Split database" model where tables are stored outside the zip archive and as a bonus you get a more up-to-date database engine.
There are a few options on how to accomplish this but the one that works best for me
[Wizard] Create a new 'split' HSQL 2.x database
Once the new database is created I copy all the tables, queries, forms and reports from the Embedded database into the new "Split database".
Be aware that with the "Split database" the table design GUI will not be able to edit existing tables, so you must use SQL commands for table edits. The table design GUI is somewhat limited so I usually don't bother with it and use SQL commands to create my tables. Once you get used to doing that way it isn't much more difficult than the GUI and you have better control.

[lewisbarry92]

UnklDonald418, thank you very much! I will definitely do this. I'm reading up on it now. I do have a silly question, how do you copy the tables, forms and reports from the existing db to the new split one? I just found another idiot question....how do I find out the version of embedded hsqldb I'm running? I found some documentation for converting a version of hsqldb to to the new split database file, but I can't find the version I'm running currently.

And thanks again!!

Barry

[UnklDonald418]

You can copy tables, queries, forms and reports with either drag and drop or Cut (Ctrl-C) and Paste (Ctrl-V).
When copying tables you will get a Copy Table dialog. Select the Definition and data option and then select Create
Looking in the lower left corner of the main database window if you see Embedded database you are running HSQLDB version 1.8 there are no other options. If instead of Embedded database you see JDBC then you are running a Split database.
To display the version number on a Split database run the following query

Code: Select all   Expand viewCollapse view

SELECT * FROM "INFORMATION_SCHEMA"."SQL_IMPLEMENTATION_INFO";

[UnklDonald418]

I've also been looking at your first question.
As you found ORB uses nested groups but you are looking to display sequential groups. So, you would need to design a query to build the sequential lists that could then serve as the data source for the ORB report. It would likely be a rather complex query involving a series of stacked sub-queries tied together with UNION ALL statements. Do you have the SQL skills to produce such a query?
Database design is not an intuitive process, it takes study ( looking at how similar databases are constructed ) and experience.
For instance IF the “person” table has Parents field(s) a query can easily display sibling(s) without a “sibling” table. Similarly, children could be identified using the Parents field(s). Actually, the “sibling” and “children” tables are likely in violation of the rules of relational database Normalization.
That said, you might create a View for “siblings” and another for “children” . Views look like tables while adhering to the rules of relational database Normalization.

[lewisbarry92]

UnklDonald418, I wish first, to say thank you again for all of your help!! Second, I am happy to report that I'm up and running on the split database and have successfully moved all of my tables, queries, forms, reports and all my test data. I am thrilled beyond belief. I did lose one report, that just simply would not copy and paste...mystery for another day..., but overall, the process went very smoothly. Thank you for the query for getting the version. I'm just now finding and getting familiar with the hsqldb data dictionary tables and views.

So, now that I'm on a stable platform, I can go back to building my little application.

UnklDonald418, thank you again for you help, it is most appreciated!

Barry

[lewisbarry92]

I've also been looking at your first question.
As you found ORB uses nested groups but you are looking to display sequential groups. So, you would need to design a query to build the sequential lists that could then serve as the data source for the ORB report. It would likely be a rather complex query involving a series of stacked sub-queries tied together with UNION ALL statements. Do you have the SQL skills to produce such a query?
Database design is not an intuitive process, it takes study ( looking at how similar databases are constructed ) and experience.
For instance IF the “person” table has Parents field(s) a query can easily display sibling(s) without a “sibling” table. Similarly, children could be identified using the Parents field(s). Actually, the “sibling” and “children” tables are likely in violation of the rules of relational database Normalization.
That said, you might create a View for “siblings” and another for “children” . Views look like tables while adhering to the rules of relational database Normalization.

I agree with you in that it will need to be the query that drives the report. I'm an old, ex, Oracle programmer. I worked with Oracle forms 4.5 and Oracle Report Builder 2.5 Smart Client way back in the 90's just before everything went to the web. I don't know much about this flavor of sql, but I've written a lot of sql,dml,ddl statements and pl/sql for my programming language. It's been a long time, but that's why I'm doing this, just to relearn and get the brain going again, and to learn something new. It'll be fun! I doubt that my db is in 3rd normal form, but it's not been de-normalized either. It is primarily a person table with a bunch of look up tables around it. Very little else going on. (At least for now), there are no many to many joins, no intersection tables, no self-joins to the person table....like I said, not too much going on. Thought of a view, but can't really see how that is going to solve my grouping problem in the report. Yeah....I think it's going to be a big hairy query...lol. I'll keep you posted on my progress, and may even need a little help with the syntax, but we'll see.

Thanks again for all of your help! And thanks for taking an interest in my little project!

Barry

[UnklDonald418]

I don't believe the basics of SQL have really changed since the 1990's, however, contemporary versions do have more functions.
If you haven't already, you can find the various versions of HSQLDB at
https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_3/
The zip archives all contain both pdf and html versions of the user guide in the hsqldb\doc\guide\ folder.
One of the syntax issues that differentiate Oracle and HSQLDB is the requirement to enclose table and column names in double quotes and strings with single quotes. Although, if the table and column names are upper case without spaces the double quotes aren't required.

It is easy to upgrade the version of HSQLDB for a Split database. Just copy the hsqldb.jar into the driver sub-folder of your Split database folder. You can also check the version of a hsqldb.jar file by double clicking on the jar file and selecting OK to the Connection dialog. Then expand the Properties heading in the left pane.
Be aware that there have been some compatibility issues reported using version 2.4.0 with Base
HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Good Luck with your project.

[Sliderule]

Just as additional note . . . if you upgrade to a 'newer' version of HSQL ( rather than the Embedded Version - 1.8.0.10 ) you could use sqltool.jar to create a HTML output file of your report.

By using sqltool.jar ( in the lib directory of the HSQL 2.4 download ), when creating your report . . . your output can come from multiple Select statements with spacing as you desire, and, defined as an HTML file ( so it can be viewed in any browser ) exactly as you desire.

NOTE: Documentation for sqltool.jar is located in the \HSQL\doc\util-guide directory as file util-guide.pdf .

[lewisbarry92]

Well, the short answer is this problem is solved! The big, hairy query query turned out to be neither very big, nor very hairy.

CREATE VIEW "FAMILY" AS
SELECT "P".*, "C"."c_first_name", "C"."c_middle_name", "C"."c_last_name", "C"."c_gender", "C"."c_child_type", "C"."c_birthday", "C"."c_deathday" FROM "Person" "P", "Children" "C" WHERE "P"."Person_id" = "C"."person_id"
UNION ALL
SELECT "P".*,"S"."s_first_name", "S"."s_middle_name", "S"."s_last_name", "S"."s_gender", "S"."s_sibling_type", "S"."s_birthday", "S"."s_deathday" FROM "Person" "P", "Sibling" "S" WHERE "P"."Person_id" = "S"."person_id"
UNION ALL
SELECT "P".*, "SP"."sp_first_name", "SP"."sp_middle_name", "SP"."sp_last_name", '', "SP"."Spouse_type", "SP"."sp_married_date", "SP"."sp_separated_date" FROM "Person" "P","Spouse" "SP" WHERE "P"."Person_id" = "SP"."person_id"

Used the query to create a view.
Used the view as the basis for the report.
In the report, I had it break of person_id and then on child_type/s_sibling_type/Spouse_type Column from the view.
I put all the person data in the person_id header.
Then I had it break on the child_type/s_sibling_type/Spouse_type Column, which is used as a "Label" for the different types of immediate family, i.e. brothers, sisters, spouses, etc., and the remaining data...the names, birthdays, marriage dates are left in the detail section and are displayed in sequential order.

Thank you so much for your help UnklDonald418, you're help was immeasurable! Sliderule, thank you so much for the info. I'm sure I'll be using it for some more advanced reporting down the road.