Introduction:

I have read some questions or queries on scheduling reports for dynamic recipients based on the table column of the report. Also, I had a few issues while working on the same requirements. Hence, I have started to write this article. I think it will help community users to work on this type of requirement in the future.

Create a scheduled report which will trigger based on conditions and send it to the dynamic receipts. Dynamic recipients may be assigned to users, the assignee’s manager, etc.

Use cases:

1. Create a scheduled report which will trigger based on some conditions and send it to dynamically assigned to users.
2. Create a scheduled report which will trigger with conditions and send the incidents report to Managers of the assignment group.
3. Create a scheduled report to send to the assigned_to users and his/her managers for pending approval of requests.

Procedure:

Need to follow the following procedure.

Step 1: Create a report and add the “assigned_to” or “assigned_to.manager” columns in the report.

Example- Create a report for example- incidents opened last week with assigned columns in it.

Step 2: Scheduled the same report run as on-demand.

Step 3: Create a scheduled script to update the dynamic assigned to the list.

Here is the script:

updateDynamicRecipients();

function updateDynamicRecipients(){
    var scheduleReport = new GlideRecord('sysauto_report');  // glide the Scheduled report
    scheduleReport.get("sys_id of report"); //Sys ID of your schedule Report

    var recipients = [];
    var tablename = scheduleReport.report.table;
    var query = scheduleReport.report.filter;

    var gr = new GlideRecord(tablename);
    gr.addEncodedQuery(query);
    gr.query();
    while (gr.next()) {
        recipients.push(gr.assigned_to.sys_id.toString());  // push the assigned _to users in recipients array
    }
    // gs.log(recipients.toString());
    // gs.log(recipientsManager.toString());

    var arrayUtil = new ArrayUtil();
    finalRecipients = arrayUtil.unique(recipients);   // unique elements

    scheduleReport.user_list = finalRecipients.join(',');
    //  gs.log("User list: " +current.user_list);
    scheduleReport.update();

    SncTriggerSynchronizer.executeNow(scheduleReport); //execute schedule report

}

Step 4: If need to include assigned _to Manager or assignment groups manager, then use the script.

updateUsersListWithManager();

function updateUsersListWithManager(){
    var scheduleReport = new GlideRecord('sysauto_report');  // glide the Scheuled report
    scheduleReport.get("42f87abc2f37201002454ae72799b66f"); //Sys ID of your schedule Report

    var recipients = []; var recipientsManager = [];
    var tablename = scheduleReport.report.table;
    var query = scheduleReport.report.filter;

    var reportgr = new GlideRecord(tablename);
    reportgr.addEncodedQuery(query);
    reportgr.query();
    while (reportgr.next()) {
        recipients.push(reportgr.assigned_to.sys_id.toString());  // push the assigned _to users in recipients array
        recipientsManager.push(reportgr.assigned_to.manager.sys_id.toString());  // push the assigned _to managers in recipientsManager array
    }
    //  gs.log("recipients array: " + recipients.toString());
    //  gs.log("recipients manager array: " + recipientsManager.toString());

    var arrayUtil = new ArrayUtil();
    arrayUtil.concat(recipients, recipientsManager);
    var finalRecipients = arrayUtil.unique(recipients);   // unique elements

    //  gs.log("finalRecipients: " + finalRecipients);

    scheduleReport.user_list = finalRecipients.join(',');

    //  gs.log("User list: " + scheduleReport.user_list);

    scheduleReport.update();

    SncTriggerSynchronizer.executeNow(scheduleReport); //execute schedule report

}

Step 5: For earlier versions of Orlando, the current object is working in the scheduled job conditional script. we can directly use the below script in the conditional option to dynamically update the recipients.

updateDynamicRecipients();


function updateDynamicRecipients(){

    var recipients = [];

    //  gs.log("current.report.filter:"+ current.report.table);
    //  gs.log("current.report.filter:"+ current.report.filter);

    var scheduledGlideRecord = new GlideRecord(current.report.table);
    scheduledGlideRecord.addEncodedQuery(current.report.filter);
    scheduledGlideRecord.query();
    while (taskQueryGR.next()){
        recipients.push(scheduledGlideRecord.assigned_to + '' );

    }

    // gs.log(recipients.toString());

    var arrayUtil = new ArrayUtil();

    var finaRrecipients = arrayUtil.unique(recipients);   // unique assigned to

    current.user_list = finaRrecipients.join(',');
    gs.log("User list: " +current.user_list);

    current.setWorkflow(false);
    current.update();
    current.setWorkflow(true);
    if (senderArray.length > 0){
        return true;
    }

    return false;

It will update the user's list dynamically and send the reports to recipients.

Check the mail logs.

In addition, to that, we can use the following methods of mail script for reports. We can specify copied and blind copied recipients by using the email object within a mail scripts.

email.setFrom("Servicedesk <servicedesk@testmail.com>");  // to set the setFrom mail address

email.setSubject("Subject of the report");  // to set the mail Subject for notification

email.addAddress("cc","sagar.pagar@testmail.com","Sagar Pagar"); // to add CC users in notification

email.addAddress("bcc","sagar.pagar@testmail.com","Sagar Pagar");  // to add BCC users in notification

Conclusion:

In this way, we can schedule a report with dynamic recipients.

Please provide your inputs and give suggestions if any. I would also like to hear a few use cases which I can build for you.

Feel free to mark helpful and bookmark this article.

Thanks,

Sagar Pagar