Report based total vs non report based total on 12.2.1.4.210915

Øyvind

hi, we are having a strange result where the sum is incorrect with the report based total and correct without.

metalink tells me that this has been a known bug in previous versions. I also do remember seeing this issue in previous versions. Im currently telling our end users to avoid using report based total as a workaround.

The report is really simple. It has an amount (defined as SUM) and a voucher text, defined as VARCHAR(200). the same issue occurs both if the voucher text is excluded, and if it is included. If removed totally, amount is of course correct

Further analysis shows that a duplicate voucher text is causing this. If voucher text XXX occurs twice in the result, grand total shows incorrect with report based total. For example if voucher text is XXX and amount is 100 000 on row 1, and 800 on row 2, it seems to pick only 100 000 in the grand total. I also see this if I export the result to Excel. I.e:

XXX 100000

XXX 100

Grand total: 100000

when I look at the logical query, the report based total causing the incorrect sum is based on REPORT_SUM

SELECT

  0 s_0,

  "GL"."GL"."Voucher Text" s_1,

  "GL"."GL"."Amount USD" s_2,

  REPORT_SUM("GL"."GL"."Amount USD" BY ) s_3,

  SUM("GL"."GL"."Amount USD" BY ) s_4

FROM "GL"

WHERE

("GL"."GL"."Voucher No" = 1234)

FETCH FIRST 5000001 ROWS ONLY

Øyvind

to answer my own question:

I figured out that this happens when the text has a space in the end. trim(GL."Voucher Text") resolves the issue