Issue with aggregate query

[jefff1]

Opening this question to the SN Community in the hopes that I'm making a simple mistake that someone can easily point out...

I'm trying to build a simple aggregate query of the time_card table that will list the total hours spent on each project. I'm grouping by the top task so that time card entries for both projects and project tasks are accounted for and summed and so that the resulting list will be of the unique projects.  

var gr = new GlideAggregate('time_card');

gr.addQuery('u_type','PMO Project');

gr.addQuery('total','>','0');

gr.groupBy('task.ref_planned_task.top_task');

gr.addAggregate('SUM','total');

gr.query();

while(gr.next()){

        var hrs = gr.getAggregate('SUM','total');

        var project = gr.task.ref_planned_task.top_task.name;

        gs.log('Project:' + project + ' Total Hours:' + hrs );

}

I have no problem getting the results to list out the summed aggregate total hours but I can't figure out how to simply add in the project number or title(short_description).   Every variation of the project declaration results in either an empty variable or "undefined".

Sample results:

*** Script: Project: Total Hours:14.00

*** Script: Project: Total Hours:6.00

*** Script: Project: Total Hours:9.00

Thank you,

Jeff

[salemsap]

Try this and lemme know the result.

var gr = new GlideAggregate('time_card');

gr.addQuery('u_type','PMO Project');

gr.addQuery('total','>','0');

gr.groupBy('task.ref_planned_task.top_task');

gr.addAggregate('SUM','total');

gr.query();

while(gr.next()){

        var hrs = gr.getAggregate('SUM','total');

        var project = gr.task.number;

        gs.log('Project:' + project + ' Total Hours:' + hrs );

}

[jefff1]

Hi Alex,

Thank you for replying.   When I ran your script, the project results were undefined.

*** Script: Project:undefined Total Hours:14.00

*** Script: Project:undefined Total Hours:6.00

*** Script: Project:undefined Total Hours:9.00

I was able to get the results listed correctly by task.name but since this lists both projects and project tasks, I'm trying to get the task's top task to be used for the output.   This worked fine in the original groupBy query to get the hours.   I just cant seem to figure out how to also get the project number the hours are aggregated for.

Jeff