Report with serial chart not showing data when using SQL for data band

Support
,
#1

Hi All,

in my application I have a report as chart, which shows the fx development for 3 currencies.
For the data band I use the following native SQl query:

SELECT
GOVEIT_EXCHANGE_RATE.VALID_FROM AS fxdate,
GOVEIT_CURRENCY.CODE AS base,
GOVEIT_EXCHANGE_RATE.RATE AS “rateEUR”,
GOVEIT_EXCHANGE_RATE1.RATE * GOVEIT_EXCHANGE_RATE.RATE AS “rateUSD”,
GOVEIT_EXCHANGE_RATE2.RATE * GOVEIT_EXCHANGE_RATE1.RATE * GOVEIT_EXCHANGE_RATE.RATE AS “rateGBP”
FROM
GOVEIT_EXCHANGE_RATE INNER JOIN
GOVEIT_CURRENCY ON GOVEIT_EXCHANGE_RATE.SOURCE_ID = GOVEIT_CURRENCY.ID INNER JOIN
GOVEIT_EXCHANGE_RATE_TYPE ON GOVEIT_EXCHANGE_RATE.EXCHANGE_RATE_TYPE_ID = GOVEIT_EXCHANGE_RATE_TYPE.ID INNER JOIN
GOVEIT_EXCHANGE_RATE GOVEIT_EXCHANGE_RATE1 ON GOVEIT_EXCHANGE_RATE1.EXCHANGE_RATE_TYPE_ID =
GOVEIT_EXCHANGE_RATE_TYPE.ID
AND GOVEIT_EXCHANGE_RATE1.SOURCE_ID = GOVEIT_EXCHANGE_RATE.DESTINATION_ID INNER JOIN
GOVEIT_CURRENCY GOVEIT_CURRENCY2 ON GOVEIT_EXCHANGE_RATE.DESTINATION_ID = GOVEIT_CURRENCY2.ID INNER JOIN
GOVEIT_CURRENCY GOVEIT_CURRENCY1 ON GOVEIT_EXCHANGE_RATE1.DESTINATION_ID = GOVEIT_CURRENCY1.ID INNER JOIN
GOVEIT_EXCHANGE_RATE GOVEIT_EXCHANGE_RATE2 ON GOVEIT_EXCHANGE_RATE2.EXCHANGE_RATE_TYPE_ID =
GOVEIT_EXCHANGE_RATE_TYPE.ID
AND GOVEIT_EXCHANGE_RATE2.SOURCE_ID = GOVEIT_EXCHANGE_RATE1.DESTINATION_ID INNER JOIN
GOVEIT_CURRENCY GOVEIT_CURRENCY3 ON GOVEIT_EXCHANGE_RATE2.DESTINATION_ID = GOVEIT_CURRENCY3.ID
WHERE
GOVEIT_EXCHANGE_RATE1.VALID_FROM = GOVEIT_EXCHANGE_RATE.VALID_FROM AND
GOVEIT_EXCHANGE_RATE.DELETED_BY IS Null AND
GOVEIT_CURRENCY.DELETED_BY IS NULL AND
GOVEIT_EXCHANGE_RATE_TYPE.NAME = ‘Actual’ AND
GOVEIT_EXCHANGE_RATE_TYPE.DELETED_BY IS NULL AND
GOVEIT_CURRENCY1.DELETED_BY IS NULL AND
GOVEIT_EXCHANGE_RATE1.DELETED_BY IS NULL AND
GOVEIT_CURRENCY1.CODE = ‘USD’ AND
GOVEIT_CURRENCY2.DELETED_BY IS NULL AND
GOVEIT_CURRENCY2.CODE = ‘EUR’ And
goveit_currency.code = ‘CHF’ AND
GOVEIT_EXCHANGE_RATE2.DELETED_BY IS NULL AND
GOVEIT_EXCHANGE_RATE2.VALID_FROM = GOVEIT_EXCHANGE_RATE.VALID_FROM AND
GOVEIT_CURRENCY3.CODE = ‘GBP’ AND
GOVEIT_CURRENCY3.DELETED_BY IS NULL AND
GOVEIT_EXCHANGE_RATE.TENANT_ID = ‘ibm’
ORDER BY
fxdate

When I go to the SQL console and run the query, I get the result rows as expected.

Since update 7.2.2 I suddenly don’t get data in the chart …

I attached the respective report here.FX development CHF-EUR-USD-GBP (broken).zip (7.1 KB)

Does anyone have an idea what could be the root cause?

#3

Hi,
I tried to run your report and in my environment it works OK.
Could you please answer a couple of questions:
Which version of the platform did you use before the update to 7.2.2?
Which DBMS do you use?
Which entities ere tenant-specific?

#4

Hi Rostislav,

I made al the updates in sequence, means 7.2.1

DBMS: PostgreSQL 12.2 (Ubuntu 12.2-2.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

All entities are tenant-specific.

Can it be, that there is somewhere a kind of setting/configuration for number of records read or buffered?

The reason why I’m asking this is because the exchange_rate table has quite some records …

#5

The table with exchange rates has more than 42000 records …

#6

Attached the three tables which I backed up with pgAdmin4.
Tables.zip (1.6 MB)

#7

I just deleted tons of records and reduced the number of records to ~3500. Unfortunately the same problem:

  • In the SQL console I get results when using the same query as in the report
  • The report with the serial chart is empty

Conclusion: Something with the “reporting” or “chart” addon isn’t working anymore since the update …