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]

Hi,

Your script include seems to return an array of objects, containing both incident record and sla record.

That will not work when query in list or in a report.

You have to return something that is valid for the query for it to work.

If you are attempting to retrieve multiple records, I do believe you also have to change the condition operator from "is" to "is one of" and then input the script include which would return one or more comma-separated sysIDs.

[PriyanshuVerma1]

Hey, can you please tell me what should i return then. What should i pass that i get the information that i need.

[OlaN]

You should return something that evaluates to a comma separated string of (in this case) sysIDs.

Something like this:

005d500b536073005e0addeeff7b12f4,02826bf03710200044e0bfc8bcbe5d3f,02826bf03710200044e0bfc8bcbe5d55  

Providing a simple example on how to:

var slaGR = new GlideRecord('task_sla');
// add your query as needed
slaGR.setLimit(3);  // remove limit after testing
slaGR.query();

var listOfSysID = [];

while(slaGR.next()){
    listOfSysID.push(slaGR.getUniqueValue());
}

return list.join(',');

[PriyanshuVerma1]

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()) {
            
            slaList.push(
                gr.getUniqueValue()
            );
        }

        return slaList;
    },

    type: 'LastAttachedSLAUtils'
};

I am using this script now as you advised. and below is the screenshot of report filter, but still i am not able to get results :