Report on Managers who do not have delegates set up

[juliekirkeeide]

Hello. I am using the reporting module (tried classic UI and New) to try to build a report showing which managers of groups do not have a delegate set up. I'm not having any luck. 

Looking for Groups / Managers / Delegate = False

In the classic UI I cannot figure out how to pull in data from a Related List

In the new UI, I cannot figure out which table to use that would allow me to pull in a related list that would result in showing all the groups, who the manager is and list the delegate or <empty> if they don't have a delegate set up.  

Again, I'm not concerned with managers (users) who do have delegates set up but only with those who do not.

Any tips appreciated!

Thank you,

Julie 

[mdash]

One indirect (and simpler) way to achieve this via below steps:

1)create a field on the User table (sys_user) : Has Delegate? (Yes/No type).
2)Set up a business rule for insert/update and one fix script for existing records that will check if there is any record present in the Delegate table where user is current user. If Yes, it will mark the Has Delegate field as True, otherwise False.
Example of Business RUle:
Table: sys_user_delegate
When to run: After - Insert, Update
Script:

(function executeRule(current, previous /*null when async*/) {

	var grDelegate= new GlideRecord('sys_user');
	grDelegate.addQuery('sys_id',current.user);
	grDelegate.query();
	if(grDelegate.next()){
		grDelegate.u_has_delegate=true;
		grDelegate.update();
	}

})(current, previous);

3)Similarly, you can write a fix script that will loop through each Delegate record and find the matching User and set the Has Delegate to True.

4)Then it will become very easy to pull a report. You can simply report on Group table where Group.Manager.Has Delegate is False.

Like this:

That should do it. 

If you need more inputs on the above script, I would be glad to help.

Thank You

[juliekirkeeide]

Thank you mdash. This is definitely an option if I can't pull the data out with existing reporting functionality

[SanjivMeher]

You need to create a report on the sys_user_group table, and add the columns Name, Manager, Manager->Delegate, should give you the results.

[juliekirkeeide]

Sanjiv, is that on the classic UI or new UI?   Manager -> Delegate isn't an option to chose on the classic UI since it is a related list.