On-Call Reporting

[Sandy7]

Is there any way to schedule a report for an On-Call schedule?

We want a report automatically generated (NOT having to go into the module to the Schedule Reports (under On-Call), you have to manually do that. 

Can someone please tell me if there's a way to pick a specific calendar and have a report that shows who's on call and have it actually scheduled?

[Sandy7]

Hi - no, no update at all. I will keep you posted though .. but I am thinking we won't have much luck. 

[conanlloyd]

Not sure if this will help you, but here's a scheduled job I created to execute the On-Call Report for all groups with active schedules & rosters.  Since it will populate the v_rotation table when it runs, you could force the report right after this job executes.

//Run the On Call Schedule Report to generate current rosters in the v_rotation table
var groups = [];
var gr = new GlideRecord('sys_user_group');
  gr.addQuery('JOINsys_user_group.sys_id=cmn_rota.group!active', '=', 'true');
  gr.addOrderBy('name');
  gr.query();
 
  while (gr.next()) {
    groups.push(gr.sys_id.toString());
  }
 
var fireNow = new OCRotation();
 
fireNow.vRotationHandling(gs.now(), gs.now(), groups.join(','));

[Mark_Schmidt]

Hello. I tried the script above that conanlloyd posted, but couldn't get it to work without some additional tweaks. This worked for me, YMMV:

1. There is a missing space after JOIN in the query string (Not sure if this really mattered).
2. Instead of gr.addQuery it needs to be gr.addEncodedQuery (This is the magic that made it work)
3. Instead of gr.addOrderBy it's simply gr.orderBy. 

Here are the tweaks I made in the code that got it to work. I hope this helps anyone having the same problem:

//Run the On Call Schedule Report to generate current rosters in the v_rotation table
var groups = [];
var grGroup = new GlideRecord('sys_user_group');
//grGroup.addQuery('JOINsys_user_group.sys_id=cmn_rota.group!active', '=', 'true');
grGroup.addEncodedQuery('JOIN sys_user_group.sys_id=cmn_rota.group!active', '=', 'true');
//grGroup.addOrderBy('name');
grGroup.orderBy('name');
grGroup.query();

while (grGroup.next()) {
    groups.push(grGroup.sys_id.toString());
}
var fireNow = new OCRotation();

fireNow.vRotationHandling(gs.now(), gs.now(), groups.join(','));

[Mark_Schmidt]

As a further enhancement, I was able to get the script to work in looking at schedules for the next 7 days. Simply drop this script into a scheduled job that exports to your format of choice. The data should match exactly what can be found on the On-call Rotation Schedule Report, but allows for automation/exporting of the data without having to run the report manually.

//Run the On Call Schedule Report to generate current rosters in the v_rotation table
var groups = [];
var grGroup = new GlideRecord('sys_user_group');
grGroup.addEncodedQuery('JOIN sys_user_group.sys_id=cmn_rota.group!active', '=', 'true');
grGroup.orderBy('group');
grGroup.query();

while (grGroup.next()) {
    groups.push(grGroup.sys_id.toString());
}
var fireNow = new OCRotation();
// Set the end date out 7 days.
var eDate = new GlideDateTime(gs.now());
eDate.addDays(7);
fireNow.vRotationHandling(gs.now(), eDate, groups.join(','));