Sum of Number Fields on Reports and List views is not correct with OR Conditions on GlideList fields

[Luis Franco]

Hi, experts.

We are watching in production several users complaining of issues with the single score reports when the conditions include OR Conditions. For example take the reports on devageaspt: "Issue on Sum on GlideAggregate" and "Issue on Sum on GlideAggregate 2".

When we are putting an Or conditions on the field collaborators and submmited by (both of them OOTB). The 2 demands should sum the field High Level Estimate to 60 and the single score and the list calculations give 160. Please check in the annex

Steps to reproduce:
One example with the idea table but I believe it will have the same bug on any table.

1- create 2 ideas

2- add more than one user to the Collaborators GlideList and put one user in both ideas.

3- add value to the Financial Benefits

4- Create a single score report on the ideas table and query collaborators contains the common user and sum on the Financial Benefits field. The value should be correct.

5- Add a OR condition that submitted Is the common user.

The value of the report is multiplied for each idea the number of users there are on the colaborators list.

Most Probable Cause:

The GlideAggregate is completely mixed up with the ^OR condition on the GlideList field. It multiplies each original record times the number of elements in the GlideList.

I managed to reproduce this by code with following script:

<code>

var st='idea.submitter=0fcce46bdb373640ec06fd431d9619dc^ORidea.collaboratorsLIKE0fcce46bdb373640ec06fd431d9619dc';

var count = new GlideAggregate('dmn_demand');
count.addEncodedQuery(st);
count.addAggregate('SUM', 'u_hle');
count.query();
var all=0;
var numerecords=0;
var aTotalCost=0;
while (count.next()) {
var allTotalCost = 0;
allTotalCost = count.getAggregate('SUM', 'u_hle');
all+=allTotalCost*1;
aTotalCost = count.getValue('u_hle');
if ( 1*aTotalCost >0 && 1*allTotalCost>0) {
numerecords+=1*allTotalCost/aTotalCost;
} else {
numerecords++;
}
gs.print('Unique field value: ' +count.number+ "/ Value: "+ aTotalCost + ', SUM = ' + allTotalCost + ', ' + allTotalCost/aTotalCost + ' records');
}
gs.print(all);
gs.print('NumRecords GlideAggregate: '+numerecords);

var gr=new GlideRecord('dmn_demand');
gr.addEncodedQuery(st);
gr.query();
gs.print("Num Records GlideRecord: "+gr.getRowCount());
all=0;
while (gr.next()) {
all+=gr.u_hle;
gs.print(gr.number+": "+gr.u_hle);
}
gs.print(all);

</code>

The Output is the following:

*** Script: Unique field value: / Value: 0, SUM = 0.00, NaN records
*** Script: Unique field value: / Value: 6, SUM = 6.00, 1 records
*** Script: Unique field value: / Value: 70, SUM = 420.00, 6 records
*** Script: Unique field value: / Value: 80, SUM = 480.00, 6 records
*** Script: Unique field value: / Value: 81, SUM = 81.00, 1 records
*** Script: 987
*** Script: NumRecords GlideAggregate: 15
*** Script: Num Records GlideRecord: 7
*** Script: DMND0029518: 70
*** Script: DMND0028725: 81
*** Script: DMND0029505: 80
*** Script: DMND0029519: 0
*** Script: DMND0029506: 0
*** Script: DMND0029507: 0
*** Script: DMND0029284: 6
*** Script: 237

[0:00:00.024] Total Time

Bottom Line:

Did you ever got this issue!? This is something that can occur with reports created by any user. I had the issue on a Query Business Rule to reduce visibility on the table for some users.

Do you know how to solve or create a workaround?

[Slava Savitsky]

I wonder if that could be caused by dot-walking. Have you tried executing a similar GlideAggregate query against the Idea table directly?

[Luis Franco]

yes. If you try this on the idea record and sum the financial benefits field the result is the same. 

From my analysis this is happening on all tables with glidelists.

[Adam Stout]

Have you opened up a case on HI for this issue?

[Luis Franco]

yes. They are still analysing it.

If they create a problem request I will share here to create "momentum" :).

Thank you.