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

Niklas Peterson · ‎08-20-2023

Hi,

This can be achieved with related list conditions.
https://docs.servicenow.com/bundle/vancouver-platform-user-interface/page/use/using-lists-v3/task/cr...

Create a report for User table and use Task table related list for related list conditions.

Regards,
Niklas

Niklas Peterson · ‎08-20-2023

Something like this:

Regards,
Niklas

AnveshKumar M · ‎08-20-2023

Hi @rosti ,

You can use a Client Callable Script Include to get the sys_id of users who closed less than 5 tasks and you can call that in report filter condition. You can use the following method in ScriptInclude to get the user sys_ids of users with closed tasks less than 5.

getTasks: function(){

var gr = new GlideAggregate('task');
gr.addEncodedQuery('closed_atONLast 30 days@javascript&colon;gs.beginningOfLast30Days()@javascript&colon;gs.endOfLast30Days()');

gr.addAggregate('COUNT','assigned_to');

gr.query();

var users = [];

while(gr.next()){

if(gr.getAggregate('COUNT','assigned_to') < 5){

users.push(gr.assigned_to);

}
}

return users.join(',);

}

Thanks,
Anvesh

rosti · ‎08-20-2023

Thanxs AnveshKumar M this

Do you have some time to give me a litle step by step guide how to set it up? or pointy me to some KB that could help me ?