I’m using a pivot table from the report generator. I am using a filter on my SQL (where clause) to limit the number of rows being selected, but my pivot table does not show the same count as an SQL count(*).
Here’s my target count:
select count(*) from countpivotmv where purchdate >= '01/01/2019' and maildate >= '01/01/0001';
count
-------
55402
(1 row)
Here’s what I get from the pivot table:
Here’s the report data band:
As you can see, it is a straight select of the same table as my select above and the filter data is the same.
Yet my resulting count is NOT the same.
What am I doing wrong?
Would it work better if I turned this into XML and a screen rather than using the Report Generator?
As a test, I added a new Table output based on the total count to see what it was pulling. I got the exact same number as the pivot table, so the problem is in the data generation and not the pivot table itself.
Does anybody have any idea why a select statement might not return all the rows when executed from the Report Generator vs the psql command prompt? (Postgres v13 database, Cuba Platform v7.2.11)
FOUND IT!!! The problem is the parameters being generated as timestamps and not dates. By debugging Yarg, I found it is actually executing the following query:
select count(*) as totalcount
from countpivotmv m
where m.mailDate between '0001-01-02 23:56:02-05' and '9999-12-31 00:00:00-05'
and m.purchDate between '2019-01-01 00:00:00-05' and '9999-12-31 00:00:00-05'
This does indeed return 50,360 for the count. The parameters are defined as Date in the Report Generator.
The problem seems to be we are getting a starting mail date of ‘0001-01-02’ when I specifically entered ‘01/01/0001’.
Hi,
it looks that the problem is not in YARG, but the Run report dialog. It seems that when a very old date is set into the DateField, the value is transformed somehow using weird time zone offsets. In case of using not such old dates or running the report programmatically using the API, everything works fine.
