On-Call rotation report does not reflect shift coverage changes

[Rick Kuijpers]

Hi all,

This concerns an issue I found in reporting on on-call schedules.

I have created a regular report [A] in the standard report builder application (Reports > Create New), on the v_rotation table. Filtered on a certain group and start time of the shift records in the past month. Report is intended to provide a monthly lookback on who had on-call duties the past month, and to be sent by e-mail as an xls or pdf.

When I run the report (directly or via a scheduled report job, which is the goal) I get the correct data based on the original on-call roster, in line with the information in the on-call calendar. However, when we update the on-call calendar with shift coverage (e.g. someone is taking over a weekend shift from a colleague) this is reflected in the on-call calendar but not in the report [A]. This still shows the original roster, and does not reflect the coverage. 

This means that a monthly scheduled report will not reflect the actual duties covered by the team members, if there were shift changes by coverage.

When I run the On-Call Scheduling > Reports > Schedule Report module for the group and period, this report [B] does show the coverage shifts and reflects the correct situation. But this report [B] appears to only be available to run manually and cannot be scheduled, and .

Now, here's the interesting bit: after running "Schedule Report" in the On-Call application, i.e. creating [B], the regular report [A] will now also show the coverage shifts and reflect the correct situation!

Is this by design, as intended? Or is it a bug?

The obvious workaround would be to manually run On-Call Scheduling > Reports > Schedule Report [B] every time a shift coverage is updated in the calendar, so that those updates will be reflected in the regular report [A]. But we're not here for manual workarounds, obviously 😉

Is there a way to trigger On-Call Scheduling > Reports > Schedule Report [B] from a script so that it can be run prior to the scheduled report job for report [A]?

Thanks for thinking along with me! 

Rick

[gmihajlov · Accepted solution]

Hi Rick,

It is because of https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0750801

Add the scheduled script below to run before your scheduled report runs:

var groups = [];
var grGroup = new GlideRecord('cmn_rota');
grGroup.addEncodedQuery('active=true');
grGroup.query();

while (grGroup.next()) {
    if (groups.indexOf(grGroup.group.toString()) === -1) {
        groups.push(grGroup.group.toString());
    }
}

var fireNow = new OCRotation();

// change the startDate and endDate to those you need for the report 
fireNow.vRotationHandling(startDate, endDate, groups.join(','));