Best way to create a report for all departments that roll up to a specific department?

[Brian Bouchard]

I need to create a report for all incidents where the caller falls under a specific department, but the department we want to look at could have several layers of child departments under it.

I started writing a report that had a similar structure to:

caller_id.department = 'DeptA' OR
caller_id.department.parent = 'DeptA' OR
caller_id.department.parent.parent = 'DeptA' OR
...
...
...

but this seems really inefficient and prone to breaking if a new department layer is added in beyond what has been coded for.

Is there an better way to get a list for all the possible sub-departments and the parent department than what I outlined above?

[Community Alums]

Hi,

You can achieve this-

In your report, write the condition as below:

'caller.department.sys_id'     'is one of'       javascript: (new getChildDepartment()).getChildDepartmentMethod('nameofanydepartment')

Create a Script Include with Client Callable "True":

var getChildDepartment = Class.create();
getChildDepartment.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	getChildDepartmentMethod : function(parentDept){
		var arr = [];
		var dept = new GlideRecord('cmn_department');
		dept.addEncodedQuery('name='+parentDept+'^ORparent.name='+parentDept);
		dept.query();
		while(dept.next()){
			arr.push(dept.sys_id.toString());
		}
		return arr;
	},
	type: 'getChildDepartment'
});

So when you write name of any department in the report, say 'Inventory' for example, the script will query for all the child departments of 'Inventory' department along with 'Inventory' department and return the Sys ID's to your filter:-

And you report will look like this (I grouped by Caller.Department for illustration):

Screenshot from Department table:

[Brian Bouchard]

Thanks @shruthiub this is the right idea, but I need it to iteratively go through all the child departments, so every level is rolled up.  For example, if the highest level in the Company is "Executives" the report should roll up all incidents across the company , because every other department is somewhere 'below' Executives.

[Ankur Bawiskar]

@Brian Bouchard 

you would have to do recursive check and find all such departments

Below script would return all the sub-departments along with the parent department

Script Include: Create a Script Include with Client Callable "True":

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

	getDepartments: function(dep,arr){

		var finalArr = [];
		finalArr = arr;
		var gr = new GlideRecord('cmn_department');
		gr.addQuery('parent.name', dep);
		gr.query();
		while(gr.next()){
			finalArr.push(gr.sys_id.toString());
			if (gr.parent !=''){
				this.getDepartments(gr.name.toString(),finalArr);
			}
		}
		return finalArr;

	},

	type: 'reportingUtils'
});

Reporting: How to call this -> Dot walk from caller -> department -> sys id

Note: Ensure you send name of the Department below along with the sys_id of that department

Caller->Department->Sys ID [IS ONE OF]

javascript: new reportingUtils().getDepartments('DeptA',['DeptAsysId']);

Screenshots:

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[Community Alums · Accepted solution]

Hi @Brian Bouchard ,

Yes we can iterate through the child departments so that we can roll up every level. Amend the above script include (Client Callable TRUE) as below:

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

	getChildDepartmentMethod: function(parentDept){
		var arr = [parentDept];
		var deptArr = this.getAllDepartments(parentDept, arr);
		return deptArr;
	},

	getAllDepartments : function(parentDept, returnArr){
		var dept = new GlideRecord('cmn_department');
		dept.addEncodedQuery('parent='+parentDept);
		dept.query();
		while(dept.next()){
			returnArr.push(dept.sys_id.toString());
			this.getAllDepartments(dept.sys_id.toString(), returnArr);
		}
		return returnArr;
	},

	type: 'getChildDepartment'
});

The query on the report changes as below where I have input the 'sys_id' of the department. Since we could be dealing with 'n' number of departments as we roll down the parent-child hierarchy, its best we input the 'sys_id' of the department.

So your query will be 'Caller.Department.SysID' 'is one of' 'javascript: (new getChildDepartment()).getChildDepartmentMethod('sysIDofDepartment')'

I have created a hierarchy as below:-

And my report where I input the sys_id of "Department 1" (which is grouped by caller.department) looks like below:-

Hope it helps! 🙂

Kindly mark my comment as correct/helpful based on the impact. Thank You!