LibreOffice Base Report - Blank results / wrong data format help using LIST/COALESCE

[ZaneZorro]

Problem: Queries that perform a LIST DISTINCT statement or a COALESCE in the SELECT portion return blank on reports.

The queries return all data and work great on their own but when used for a report, the fields that are based on the LIST or COALESCE statements are returning blank and looking at the formatting of the fields in the report, it’s picking them up as a numeral, even though LIST should return a string. The underlying table fields that the LIST and COALESCE statements are querying are set to text(VARCHAR).

If I set the formatting to TEXT for those fields in the report then rather than returning the data, it returns errors in the text box such as:
Any[Type[com.sun.star.sdbc.XClob], com.sun.star.bridges.jni_uno.JNI_proxy@7ce85af2 [oid=600022c74d20;gcc3[0];e84d90162d54bac8b9446ba93ce34f, type=com.sun.star.sdbc.XClob]]

You can recreate the issue fast and easily by creating a table called “Contacts” with three columns “Name”, “Address”, “Tel”. Set all to text VARCHAR and let it set the ID primary on save. Put some rows of dummy data. Then create this query:

“Name”,
LIST(DISTINCT “Address”) AS “Address1”,
“Tel”
FROM
“Contacts”
GROUP BY
“Name”,
“Tel”

Then open a report, drag Address1 to the detail and run the report. You will see (or more accurately wont see because it will be blank) the problem.

Does anyone please know how to fix this? The query runs perfectly, so what could be causing the issue?

Running LibreOffice 24.2 (Firebird embedded)

[RobertG]

Best will be to upload an example here.
Note: You will get better results in reports if you create a view of your query. Then try to Cast the type of the field, which has been created by the function “LIST”.

[Villeroy]

Same here PowerFilter.FB.odb (55.9 KB) (query and report “List_Categories”)
Work-around when reports fail or when you need a quick report: Use Calc as report medium.
Either drag a query icon from the Base window into a Calc document or drag the icon from the data source window. The latter creates a linked database range which can be updated via menu:Data>Refresh.
Your query yields #NA! errors for the “Address1” column. Just convert that column to VARCHAR explicitly:
CAST(LIST(DISTINCT "Address") AS VARCHAR(1000)) AS "Address1" This additional CAST will not fix the report, unfortunately.
In order to fix the report in my sample:

1. right-click query “List_Categories”, choose “Create as view…” and enter some unique name, say “View_List_Categories”. A view is like a query that is stored in the realm of the database rather than Base.
2. Open the report for editing, get the report navigator, switch the report source type from “SQL statement” to “Table” and enter the view name.

[RobertG]

Have taken the example, changed the query to a view: works.
PowerFilter.FB.odb (60.9 KB)

[ZaneZorro]

awesome, thank you!