Data not showing from field that uses sql on Base report

Here is a video get field from sql querry into Base form report - YouTube
that describes a problem where some data is not displayed and some is on my report when generated from sql as opposed to from a query or table. In the video description, there is a link to download the database so that you can have a closer look.
Dropbox - LANdpLAN - Simplify your life

P.S. I’ve noticed that if I copy and paste the “quantity” field it shows up in the report. If I change the data from “quantity” to “plot” it no longer shows up in the report. If I change it back from “plot” to 'Quantity, it sill does not show up in the report.

I have tried to find the file and the report, which isn’t working. Found a database with many reports … So I have given up.

On no. In the video, you can see that it is the second report that is in question. Did you see the video?
Also, when you say “isn’t working” do you mean that the images do not load when you open the report. This would make sense because the image urls would be wrong on your computer. The report should still open and show “quantity”.

I checked your video, but didn’t check your file.

Actually i stopped at the point, where you state you don’t get a dropdown for the fields of a query, but have to enter SQL.

I use fields from queries all day.
I have to check details, but I guess you have to select your query one row higher in the dialogue like you have to select the used table. Try to set it from Field or formula to something like query in your locale. (You may have noticed: Your type of datasource changed to SQL…)

So this should work without entering SQL in that dialogue.

PS: In the video you said “working on forms”, but actually it is “reports”.

Thanks for checking. I’m sorry about the ignorant terms that I use like forms instead of reports and query instead of function or sql.

I believe I said “There is no drop down menu that I can use. I have to manually type it in.”, not “don’t get a dropdown for the fields of a query, but have to enter SQL.” For example instead of a drop down menu whee I can choose “plot” I have to type “plot”.

The SQL that I refer to is in the Content of the Data of the Report (SELECT “plot”, “initials”, “latin_name”, “Common_name”, “icon_url”, “sited”, “Habit”, “id”, “quantity” FROM “plot_plant_legend” WHERE ( “quantity” > ‘0’ AND ( “Habit” LIKE ‘Tree%’ OR “Habit” LIKE ‘Bulb%’ ) ) ORDER BY “initials” ASC, “latin_name” ASC)
I don’t understand “select your query one row higher in the dialogue like you have to select the used table.” There is no table or query with the data that I want. That is shy I have chosen “Content type” “SQL command”.

I also do not understand “set it from Field or formula to something like query in your locale” What are you refer to when you say “it”?

It seems that using fields from queries works. Using sql from queries is not working as well.

Also, I’ve noticed that copying and pasting the field works. When I edit the data field it stops working. Even if I edit it again back to the original. Ex: Data field “quantity” works after copy paste. Edit data field to “plot” and no longer works. Edit back to “quantity” and no longer works.

Ok. There is my mistake. I thought you actually had this query created but couldn’t select. So my suggestion is to create first this SQl-query and save it. Then you can select the query as datasource for the report. Actually you can only change this when you don’t have a field selected, but “the report”. I use the report navigator for this, as shown on this picture (the german Abfrage is english query):

Then you can select your fields by dropdown:

I have to test what to do with the SQL-query in the field, as you tried to use. For the moment I think it may be useful as subquery, but then it should give one value, not a column as result.


Have found no issue. Created (my own HSQLDB Base file) report based upon SQL.

Started with one table three fields, changed, added second & third tables and added & changed fields and do not see problem as you stated. Even tried a second creation and that worked also. Even used my own HSQL split DB and that (short test) worked.

The sample you provide is not manageable and videos are a pain.

Please provide a small embedded sample.

1 Like

Thanks. As you suggested I created queries for each report and saved them to the queries window. Then I used those queries in the report instead of using sql within the report to get what I wanted.