I have a small database which was in HSQLDB and I have now converted to Firebird in LO 6.1.5.2 from Debian Testing. The sql used by Firebird is different from HSQLDB and I have modified a query so that it now works with Firebird. However, when I run a report using Report Builder and data from the query, data from computed fields are not being included.
I see from Query Works but not in Report (View topic) • Apache OpenOffice Community Forum that “When Base makes a report in LibreOffice, or OpenOffice with the Report Builder extension, it runs the query through the Base parser, which can’t handle some SQL.” With LO now moving to Firebird what sql will Report Builder now accept? Is there any documentation anywhere?
Hello,
The provided link is not relevant to the question presented. Have just created a Report in Base using a query with calculated fields from a Firebird embedded DB. The only problem is a known bug regarding misplaced decimal (fixed with a CAST).
Please present more information on your problem - Sample would be best.
This is one line in the query: CASE "Month" WHEN 1 THEN 'January' WHEN 2 then 'February' WHEN 3 then 'March' WHEN 4 then 'April' WHEN 5 then 'May' WHEN 6 then 'June' WHEN 7 then 'July' WHEN 8 then 'August' WHEN 9 then 'September' WHEN 10 then 'October' WHEN 11 then 'November' WHEN 12 then 'December' END || ' ' || "Day" || "Dates" "Fulldates",
It gives the expected output when I run the query but nothing appears where “Fulldates” is included in the report.
In the query, is Run SQL command directly turned on?
I see a button to Run SQL command directly and the same item in the Edit menu when in the query design panel. If I click on either of these it changes the Switch Design View On/Off button to greyed out and back. Is Run SQL command directly turned off if the Switch Design View On/Off button is greyed out? If I try to exit the edit query panel with the Switch Design View On/Off button not greyed out I get a syntax error.
The statement used does indeed require it to be run directly - no choice. With run directly turned on (from toolbar or menu) selecting Design View is not allowed.
Hello,
There is a problem within Base, and not specific to just Firebird, dealing with alias names when Run SQL command directly is on. This affects both reports and forms displaying the data. Have seen differing degrees of when it works and when it doesn’t.
Using just your example, you can just drop the alias name (“Fulldates”) and in the use the generated name from the query (probably CONCATENATION).
Sub queries also present a problem:
Select "Total" from (SELECT "Cost" * "Qty" "Total" FROM "Rental")
but :
Select CAST("Total" AS NUMERIC(7,2)) from (SELECT "Cost" * "Qty" "Total" FROM "Rental")
will give a usable field name (“CAST”) which can be used in the report. It becomes increasingly difficult with multiple fields of this type becoming “CAST1” then “CAST2” and so on.
Now because of the limited information you have given, it is also possible there are other methods to obtain dates, Firebird certainly is more advanced than the HSQLDB embedded. There are also functions you can create & use. For example → Functions with PSQL in Firebird 3. Have tried this one myself.
Edit 2019-03-28:
Have not touched your original report as I am not comfortable with it. Instead created a small sample report. If Report Wizard is used, get error list as stated. If created in design view seems to work OK. Sample has report TestAlias. Modified query Current concatenated by dropping Fulldaes alias.
Sample ------ EFIOS courses.odb
I tried dropping the alias name. I selected CONCATENATION, which was listed in the report builder editor, and I get a long set of error messages when I try to run the report, of which the most relevant one seems to be:
*no column name specified for column number 6 in derived table
I appreciate your suggestion about a date function, though it seems something of an overkill for what I am trying to do. However, I have another item in the query that has the same problem, not involving dates,
Please post a scrubbed sample of the Base file.
I’ve linked 1 file to this message:* EFIOS courses.odb (244 kB) hosted on Box: Box
There is nothing confidential in it. I use the report to produce the html for this page: http://dpets.uk/efios/efios/efioscourses.html (currently out of date while I sort this out).
Will add edit to my answer.
As soon as I turn on sorting and grouping the error messages come up.