Export the data from On-call Schedules (v_rotation) to import in e-mail report.

[Shubham111]

There are some records which are generated in (v_rotation) table from (cmn_rota_member_list) table when we click on "Run Report" button(Kindly refer to the Screenshot attached). The entries in (v_rotation) table gets erased after some time so we want the logic code of "Run Report" button so that we can include it in a schedule job and schedule email of this reports to the user as per requirement.

[Rick Kuijpers]

Hi! I may have the same question and I'm wondering if you have found a solution. To make sure we have the same issue, can you confirm that this is what you run into:

I have created a regular report "A" in the Reports 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. When I run the report (directly or via a scheduled report job) 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 does show the coverage shifts and reflects the correct situation.

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

I will create a separate thread for this, to ask my questions on how to get around this. I will link that here once created so you can follow the discussion. Hopefully there is a solution, or at least a workaround that is not manual.

[SwarnadeepNandy]

Hello @Shubham111,

There could be a few possible steps to fix your issue. One step is to find the script that is executed by the Run Report button. You can do this by inspecting the UI action or UI macro that is associated with the button and looking for the script that generates the records in the v_rotation table from the cmn_rota_member_list table.

Another step is to copy the script to a script include or a scheduled script execution. You can do this by creating a new script include or a scheduled script execution and pasting the script that you found in the previous step. Then, you can modify the script as needed to suit your requirements, such as adding parameters, conditions, or logging.

A third step is to create a scheduled job that runs the script include or the scheduled script execution at a specified frequency. You can do this by navigating to System Definition > Scheduled Jobs and clicking the New button. Then, you can give a name to the scheduled job, select the script include or the scheduled script execution as the source, and configure the schedule details, such as start time, end time, repeat interval, and run as user.

A fourth step is to create a notification that sends an email with the report to the user after the scheduled job runs. You can do this by navigating to System Policy > Notifications and clicking the New button. Then, you can give a name to the notification, select the scheduled job as the table, choose an event or condition that triggers the notification, and configure the email details, such as recipients, subject, message, and attachments.

Not sure if this would help. Let me know in case.

Kind Regards,

Swarnadeep Nandy

[Rick Kuijpers]

@SwarnadeepNandy this is the direction I'm think as well.

My topic for my specific question (as I am not sure if @Shubham111 had exactly the same issue):

https://www.servicenow.com/community/itsm-forum/on-call-rotation-report-does-not-reflect-shift-cover...

[SwarnadeepNandy]

@Rick Kuijpers,
Thanks for your input, let me check your thread, if I can help.