Running ART 4.16. I have several LOV dynamic reports which all query from a single postgres table. They query different values mapped to the same 'display' column. The issue only arises with the ones selecting from one of the twoa timestamp fields (obs_begin_dt and obs_end_dt). There are 8 rows which match the where clause but with the two timestamp based queries, only 6 rows appear in the parameter dropdown and they are not ordered by 'description' . Another oddity is that depending on the order by clause, a different 6 will appear. However, when I run the LOV dynamic report from the report editing screen using 'Test', it returns all 8 rows. Below are examples of the queries and I'm attaching both the 'dropdown' list of values based on the obs_begin_dt LOV report as well as the 'Test' run of that same LOV report.
select proj_id,description from proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1' order by description
select proj_period_id,description from proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1' order by description
select obs_begin_dt,description from proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1' order by description
select obs_end_dt,description from proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1' order by description
Below from pg_stat_activity (note I've been tweaking the order by to see if I can find some pattern, to no avail) UI still shows results different from the query response itself.
SET application_name = 'ART - ro_ebird-sw2'
select obs_begin_dt, description from cur.proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1'
order by 1
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK. I think I've found the issue. The lov values are stored in a map with the value of the first column as the key. From the 8 rows, 3 have the same date, so only one will be taken up, ending up with 6 items in the drop down. The assumption was that if the value is the same, the result returned will be the same so the other entries would be redundant, but perhaps this may not be the case..
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Ahhh, that makes sense based on what I'm seeing. In our case I want to use the start date of a project in a report and allow the person running the report to pick the project from a pic list. And as you see, different projects can have the same start date. So definitely not redundant in our case.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Running ART 4.16. I have several LOV dynamic reports which all query from a single postgres table. They query different values mapped to the same 'display' column. The issue only arises with the ones selecting from one of the twoa timestamp fields (obs_begin_dt and obs_end_dt). There are 8 rows which match the where clause but with the two timestamp based queries, only 6 rows appear in the parameter dropdown and they are not ordered by 'description' . Another oddity is that depending on the order by clause, a different 6 will appear. However, when I run the LOV dynamic report from the report editing screen using 'Test', it returns all 8 rows. Below are examples of the queries and I'm attaching both the 'dropdown' list of values based on the obs_begin_dt LOV report as well as the 'Test' run of that same LOV report.
select proj_id,description from proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1' order by description
select proj_period_id,description from proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1' order by description
select obs_begin_dt,description from proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1' order by description
select obs_end_dt,description from proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1' order by description
Last edit: Jeff Gerbracht 2021-06-30
You could try use the SQL Logging driver for the LOV datasource and see what queries are being run in the logs.
Something I'm a bit unclear about, do these LOV queries run every time the related report run screen is loaded ? Or are the results cached.
Last edit: Jeff Gerbracht 2021-06-30
The results are not cached. The LOV queries run every time.
Yes.
Below from pg_stat_activity (note I've been tweaking the order by to see if I can find some pattern, to no avail) UI still shows results different from the query response itself.
SET application_name = 'ART - ro_ebird-sw2'
select obs_begin_dt, description from cur.proj_period where proj_id like 'EBIRD_ATL%' and is_current = '1'
order by 1
OK. I think I've found the issue. The lov values are stored in a map with the value of the first column as the key. From the 8 rows, 3 have the same date, so only one will be taken up, ending up with 6 items in the drop down. The assumption was that if the value is the same, the result returned will be the same so the other entries would be redundant, but perhaps this may not be the case..
Ahhh, that makes sense based on what I'm seeing. In our case I want to use the start date of a project in a report and allow the person running the report to pick the project from a pic list. And as you see, different projects can have the same start date. So definitely not redundant in our case.
This change should be effected in the next release.