With a glide aggregate, can you dot walk in a groupby

[pneuvil1]

When constructing a glide aggregate query, can you dot walk in the group by.

This code will produce expected results.

var res = '';
var tca = new GlideAggregate('time_card');
tca.addQuery ('week_starts_on', '>', '2020-12-31');
//tca.groupBy('top_task.top_portfolio'); // <-----****
tca.groupBy('top_task');
tca.groupBy('user');
tca.addAggregate('SUM', 'total');
tca.query();
gs.print (tca.getRowCount());
while (tca.next())
{
res = '';
for (var x in tca) res = res+ (x+' - ' +tca[x]+', ');
gs.print (res);
gs.print (tca.user.name+' - '+tca.top_task.number+' - '+tca.top_task.top_portfolio.name+' - '+tca.getAggregate('SUM', 'total'));
}

in the 'res' variable you can see that top_task has a value as well as user. Both are specified in the groupby statement.

But when you uncomment out the dot walk group by and comment out the top_task groupby as seen below. Top_task no longer has a value and it is unknown what totals are associated with what portfolios.  

var res = '';
var tca = new GlideAggregate('time_card');
tca.addQuery ('week_starts_on', '>', '2020-12-31');
tca.groupBy('top_task.top_portfolio'); // <-----****
//tca.groupBy('top_task');
tca.groupBy('user');
tca.addAggregate('SUM', 'total');
tca.query();
gs.print (tca.getRowCount());
while (tca.next())
{
res = '';
for (var x in tca) res = res+ (x+' - ' +tca[x]+', ');
gs.print (res);
gs.print (tca.user.name+' - '+tca.top_task.number+' - '+tca.top_task.top_portfolio.name+' - '+tca.getAggregate('SUM', 'total'));
}

So I am assuming glide aggregate is not designed to accommodate dot walked group by's. 

[pneuvil1 · Accepted solution]

According to Service Now, you can not. CS5164612

[Marek Meres]

Hi,

I had the same (or just similar?) issue and found an answer here.

I cannot try your code (as 'time_card' table seems not to be on my PDI) but this one works for me and it uses dot-walked aggregates:

var ga = new GlideAggregate("cmdb_health_result");
ga.addAggregate("COUNT");
ga.groupBy("ci.supported_by");
ga.groupBy("metric");
ga.query();
while (ga.next()){
    gs.info(ga.getDisplayValue("ci.supported_by")+" : "+ga.getDisplayValue("metric")+" : "+ga.getAggregate("COUNT"));
}

My results:

*** Script:  : Audit : 392
*** Script:  : Duplicate : 322
*** Script:  : Staleness : 114
*** Script: Charles Beckley : Audit : 1
*** Script: Charles Beckley : Staleness : 1
*** Script: James Vittolo : Audit : 3
*** Script: James Vittolo : Staleness : 3
*** Script: Winnie Reich : Audit : 1
*** Script: Winnie Reich : Staleness : 1

Have a try!

[pneuvil1]

thanks for you reply.

I guess the difference here is that I was actually trying to get a value by dot walking the same thing I was grouping by.

Another words, the group by would work. but trying to get the value, "tca.top_task.top_portfolio.name" would not. This according to service now.

[MPD Odijk]

The grouping works just fine. However, when displaying, I noticed that

ga.getDisplayValue("ci.supported_by")

does work, while 

ga.ci.supported_by.getDisplayValue()

doesnt.