Creating a report with <20 tasks assigned to specific assignment groups

[itd87]

Hello, 

I am trying to create a report to show the results of select assignment groups with fewer than 20 tasks (incident, change, problem) assigned to them in the past year. My goal is to see which teams in my company are not using ITSM. 

The part I'm struggling with is limiting my results to those with fewer than 20 tasks. I hoped I could use the report's aggregation count value as a variable in the filters, but I'm unable to do that.  

Any advice or thoughts on this would be greatly appreciated. Thank you! 

[arielgritti]

Hi @itd87 
Try this.

Create a report in Group (sys_user_group) table.

In the "related list conditions" select Task (task) table with the condition "Less than or equal to" 20 and in the field select assignment_group for task table field.

Please mark helpful or correct if I helped you.
This action will help other members with similar issues to find a solution.
Thanks
Ariel

[Hemanth M1 · Accepted solution]

Hi @itd87 ,

You can do this in a more dynamic way but need to configure couple of things 

1)Create a report on task table (List type report) and group by Assignment Group

2)Create Script include (Client callable script include as below)

Script here:

var getCount = Class.create();
getCount.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    assignment_group_count: function() {
        var agName = "";
        var gr = new GlideAggregate("task") 
        gr.addAggregate("COUNT");
        gr.groupBy("assignment_group");
        gr.query();
        while (gr.next()) {
            var count = gr.getAggregate("COUNT"); //count of the each assignment group 
            if (count < 11) { //only get assignment group has 10 or fewer records
                agName = agName + "," + gr.getDisplayValue("assignment_group");
            }
        }
        return agName.toString(); //retun the assignment group names

    },

    type: 'getCount'
});

3)go back to the report and add this filter condition

assignment group name is one of javascript&colon; new getCount().assignment_group_count() as below

save and run the report 😍, lt would only show assignment groups which 10 or less number of tasks

you can expand the check the records as well here.

this is scalable (you can change the count in the script include to get the different count and also you don't have to enter any assignment group manually to check.

[itd87]

Thank you for this solution! This is exactly what I was looking for.