Report script less than 5 tasks closed per user (tasktable)

rosti · ‎08-20-2023

Hey all

I have a customer who only wants to see a list report that gives only users who have closed less than 5 tasks over 30 days (All Tasks)

I see that there are limitations in the creation of a report and would like to hear from you experts how a script could be used for this and which I call via the report.

Thank you for your support.

Sonu Parab · ‎08-20-2023

Hi @rosti ,
The issue is in the encoded query line in script include.
The encodedQuery should be link this:

and I have observed that in your script line there is javascript&colon.

gr.addEncodedQuery("closed_atONLast 30 days@javascript&colon;gs.beginningOfLast30Days()@javascript&colon;gs.endOfLast30Days()");
gr.addAggregate('COUNT', 'closed_by');

could you please make these changes.
this will work.

View solution in original post

Sonu Parab · ‎08-20-2023

Hi @rosti ,
I have updated the script include If condition to check the fulfiller role. rest of the script is as it is.

  if (gr.getAggregate('COUNT', 'closed_by') < 5) { // here we are checking aggregate count

                var closesby = gr.closed_by.toString(); //Get closed by user i.e.user sys_id
                var role = "itil"; // Here  you can pass role called IT Service Management Professional. 
                var checkrole = gs.getUser().getUserByID(closesby).hasRole(role);// ckecking closed by user having fulfiller role
                if (checkrole) { // if true then and then only push sys_if into array.
                    usersys.push('' + closesby);
                }
            }

View solution in original post

rosti · ‎08-20-2023

Thanxs i will test some more and getback later

rosti · ‎08-20-2023

Hello
I didn't get it to work .there should be 2 users who have closed less than 4 tasks but I got 0 in result.

Any suggestions ?

Aslo if it possible to add if the user is an Fulfiller in the script would make it perfect .

Sonu Parab · ‎08-20-2023

could you please share your Script include script or snippet.

Sonu Parab · ‎08-20-2023

Hi @rosti ,
In your Report filter use javascript colon

I think you have taken semicolon there.

rosti · ‎08-20-2023

Yes of course.

var fetchusers = Class.create();
fetchusers.prototype = Object.extendsObject(AbstractAjaxProcessor, {
getUsers: function() {

var usersys = [];
var gr = new GlideAggregate("task");
gr.addEncodedQuery("closed_atONLast 30 days@javascript&colon;gs.beginningOfLast30Days()@javascript&colon;gs.endOfLast30Days()");
gr.addAggregate('COUNT', 'closed_by');
gr.query();
while (gr.next()) {

if (gr.getAggregate('COUNT', 'closed_by') < 5) {
// gs.info(gr.number);
var closesby = gr.closed_by.toString();
usersys.push('' + closesby);
}
}
// gs.info(usersys);
return usersys;

},

type: 'fetchusers'
});