Question

Quick Report gives wrong output when scheduled

  • 2 February 2023
  • 5 replies
  • 119 views
U
Rank
Badge +5

We have a quick report that outputs different results when it is sent via a scheduled data base task compared to when it is run manually. 

These sums are done using simple “select sum() from table where X” and are listed in rows using union. 

As you can see in the table. Only sum 7 and sum 8 are inorrect when using the scheduled quick report. The others are correct.

 

Any ideas why this might be happening?

 

Manual output: 

sum1 1263584
sum2 8608567
sum3 4872151
sum4 55863
sum5 6525876
sum6 3413890
sum7 4532101
sum8 9021000
sum9 7553101

 

Scheduled Quick Report:

sum1 1263584
sum2 8608567
sum3 4872151
sum4 55863
sum5 6525876
sum6 3413890
sum7 8428850,52
sum8 9021000
sum9 11449850,52

 

5 replies

Link
Rank
Userlevel 7
Badge +22

Hi @Unscented0296 

can you post here your sql code?

Kind regards
U
Rank
Badge +5 
select '' as "Kunde, Ort", 'Summe 1: Gruppe' as "Nr., Summen", sum(Menge) as Bestand
from IFSINFO.BT_STOCK_EOM
where Art = 'Bestand'
and kunr LIKE 'C%'

union

select '' as "Kunde, Ort", 'Summe 2: Anonym -NB' as "Nr,, Summen", sum(Menge) as Bestand
from IFSINFO.BT_STOCK_EOM
where Art = 'Bestand'
and Ort LIKE 'Anonym%'
and Artikel NOT LIKE '99%'
group by kunr

union

select '' as "Kunde, Ort", 'Summe 3: Gruppe +Anonym -NB = Board'  as "Nr, Summen", sum(Menge) as Bestand
from IFSINFO.BT_STOCK_EOM
where Art = 'Bestand'
and kunr LIKE 'C%'
or Ort LIKE 'Anonym%'
and Artikel NOT LIKE '99%'

union

select '' as "Kunde, Ort", 'Summe 4: NB = Nebenbahnen'  as "Nr, Summen", sum(Menge) as Bestand
from IFSINFO.BT_STOCK_EOM
where Art = 'Bestand'
and Artikel LIKE '99%'

union

select '' as "Kunde, Ort", 'Summe 5: Alle anderen Kunden'  as "Nr, Summen", sum(Menge) as Bestand
from IFSINFO.BT_STOCK_EOM
where Art = 'Bestand'
and Name NOT like 'Anonym'
and kunr not like 'C%'

union

select '' as "Kunde, Ort", 'Summe 6: Macher Gesamt'  as "Nr, Summen", sum(Menge) as Bestand
from IFSINFO.BT_STOCK_EOM
where Art = 'Bestand'

union

select '' as "Kunde, Ort", 'Summe 7: Auftragsbestand Gruppe'  as "Nr, Summen", 
sum(desired_qty) as Bestand
from customer_order_line
where promised_delivery_date > sysdate
and customer_no LIKE 'C%'

union

select '' as "Kunde, Ort", 'Summe 8: Auftragsbestand Andere'  as "Nr, Summen", 
sum(desired_qty) as Bestand
from customer_order_line
where promised_delivery_date > sysdate
and customer_no NOT LIKE 'C%'

union

select '' as "Kunde, Ort", 'Summe 9: Auftragsbestand Gesamt'  as "Nr, Summen", 
sum(desired_qty) as Bestand
from customer_order_line
where promised_delivery_date > sysdate

 

U
Rank
Badge +5

So it seems IFS somehow got it wrong.

I deleted the quick report, created a brand new one with the exact same query and now it’s working. That’s pretty frustrating to be honest, because from my perspective I am not doing anything differently, but suddenly the output is correct.  

Can it be that IFS saves an old/incorrect version of the quick report and keeps using it even though it changed?

U
Rank
Badge +5

So it seems IFS somehow got it wrong.

I deleted the quick report, created a brand new one with the exact same query and now it’s working. That’s pretty frustrating to be honest, because from my perspective I am not doing anything differently, but suddenly the output is correct.  

Can it be that IFS saves an old/incorrect version of the quick report and keeps using it even though it changed?

I have to correct this. Even after deleting the report and creating a new one, it sometimes gets sum7 and sum9 completely wrong when being scheduled.

J
Rank
Userlevel 4
Badge +8

Hi,

 

Is it possible that the user manually running the report is missing access to one or more sites? And the user set to be running the scheduled task has more/all sites?


BR

Johan 

Reply


Terms & ConditionsCookie settings