According to your signature you are using OpenOffice 1.4.2 which doesn't appear to be correct
https://wiki.openoffice.org/wiki/Product_Release
Please update your signature.
The standard report writer in to OpenOffice doesn't offer much flexibility, but if you install the Oracle-report-builder extension (it is built in to LibreOffice)
https://extensions.openoffice.org/en/pr ... rt-builder
You can use your query as the data source for a report. Group records on the "EventID" and add the other fields you want to appear on the Header and put the rest in the Details area of the report.
I had seen a strategy for suppressing the display of query fields so I thought I would give it a try using tables structured according to what I saw on your included images.
First the records in "CAPs1" are grouped according to "EventID" and then ranked within the groups by "CAPID"
Code: Select all
SELECT "e"."CAPID",
"e"."Description",
"e"."Response",
"e"."Closed_Status",
"e"."EventID",
( SELECT COUNT( * )
FROM "CAPs1" AS "d"
WHERE "e"."EventID" = "d"."EventID" AND "e"."CAPID" > "d"."CAPID" ) AS "RANK" FROM "CAPs1" AS "e"
ORDER BY "e"."EventID", "RANK"
I used that query to create "View01"
Then CASEWHEN statements can be used to suppress the display of fields in the final query based on their "RANK"
Code: Select all
SELECT CASEWHEN( "RANK" = 0, "Events1"."EventID", NULL) AS "EventID",
CASEWHEN( "RANK" = 0, "Events1"."Location", NULL) AS "Location",
CASEWHEN( "RANK" = 0, "Events1"."Type", NULL) AS "Type",
CASEWHEN( "RANK" = 0, "Events1"."Description", NULL) AS "Description",
CASEWHEN( "RANK" = 0, "Events1"."Prelim_Rep_Issued", NULL) AS "Prelim_Rep_Issued",
CASEWHEN( "RANK" = 0, "Events1"."Final_Rep_Issued", NULL) AS "Final_Rep_Issued",
CASEWHEN( "RANK" = 0, "Events1"."Email_Note", NULL) AS "Email_Note",
CASEWHEN( "RANK" = 0, "Events1"."Phone_Note", NULL) AS "Phone_Note",
"V1"."CAPID" AS "CAPID",
"V1"."Description" AS "Description",
"V1"."Response" AS "Response",
"V1"."Closed_Status" AS "Closed_Status",
"V1"."RANK"
FROM { OJ "Events1"
LEFT OUTER JOIN "View01" AS "V1"
ON "Events1"."EventID" = "V1"."EventID" }
The results look promising, but not all fields with CASEWHEN statements were suppressed.
Also any records in "Events1" that don't have at least one corresponding record in "CAPs1" don't display properly despite the Left Outer Join.
In an Embedded Base database when numeric values like "EventID" are NULL they are displayed as 0.
That behavior isn't seen using a split database
[Wizard] Create a new 'split' HSQL 2.x database
But even then, I've not found a way to suppress the display of Boolean fields.
All of which confirms the sage advice of
eremmel.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11