How to use script in reporting for complex query

[PriyanshuVerma1]

Hey everyone,

I have a requirement to pull a report that will have list of incidents and last sla attached to it with sla details like breach time, start time,etc.

Now I build a report on task_Sla table to do it. And created a script include to achieve "last sla attached to incidents". here is the script : 

var LastAttachedSLAUtils = Class.create();

LastAttachedSLAUtils.prototype = {
    initialize: function() {},

    // Get the last attached SLA for each incident.
    getLastAttachedSLAForIncidents: function() {
        var slaList = [];
        var gr = new GlideAggregate('task_sla');
        gr.addQuery('task.active', true); // Filter for active incidents.
        gr.groupBy('task');
        gr.addAggregate('MAX', 'sys_created_on'); // Get the latest SLA per incident.
        gr.query();

        while (gr.next()) {
            var incident = gr.get('task');
            var lastAttachedSLA = gr.getAggregate('MAX', 'sys_created_on');
            slaList.push({
                incident: incident,
                lastAttachedSLA: lastAttachedSLA
            });
        }

        return slaList;
    },

    type: 'LastAttachedSLAUtils'
};

Now i have seen a few videos on how to use script in reporting. I used sys id field to call the script include. But report is not pulling any data. Can someone let me know what i am doing wrong

[OlaN · Accepted solution]

GlideAggregate is good for counting records, getting total, average, min, max.

But in this case you are after the SysID of the specific records.

So I would advice to use GlideRecord instead.

Providing another example, hope this helps:

var slaSysID = [];
var incGR = new GlideRecord('incident');
incGR.addActiveQuery();
// add additional query if needed
incGR.setLimit(10);  // remove limit after testing
incGR.query();

while(incGR.next()){
    var slaGR = new GlideRecord('task_sla');
    slaGR.addQuery('task', incGR.getUniqueValue());
    slaGR.orderByDesc('sys_created_on');
    slaGR.setLimit(1);  // only want one record
    slaGR.query();
    if (slaGR.next()){
        slaSysID.push(slaGR.getUniqueValue());
    }
}

return slaSysID.join(',');

[PriyanshuVerma1]

Your logic worked, thanks for helping me out on this one.

[Ankur Bawiskar]

@PriyanshuVerma1 

for your script include to work fine from report filter condition; It should be client callable

Changes you need to do

1) make your script include client callable

2) use IS ONE OF in your operator and return list of sysIds from script include function

If my response helped please mark it correct and close the thread so that it benefits future readers.

[PriyanshuVerma1]

i have both the thing in place that you mentioned. It is not working. Please see if the code is correct or the way i am trying to call script include.

javascript: new LastAttachedSLAUtils().getLastAttachedSLAForIncidents();