How to send weekly scheduled report notifications to each individual assignment group including ONLY their relevant data.

[Ozgur Baykal]

I have created two barchart reports "Aging Tasks" and "Aging Interactions". We have over 100 Assignment Groups and I want to send those two reports, weekly,  to all those Assignment groups individually, including the reports showing only data  related to their own team. I do not want to create 200+ reports (for each assignment group) and 200+ "Scheduled email of reports" for this. Can we write script to do this weekly report notification task in a more automated way? If so, Can someone help/guide me to build that script? 

[Vasantharajan N · Accepted solution]

@Ozgur Baykal - Are you using assignment group in interaction if yes, then use the below code snippet.

Please make the necessary filter update and use it

//Get Incident Assignment group
var gr = new GlideAggregate("incident");
//Your actual filter in report
gr.addQuery("active=true");
gr.groupBy('assignment_group');
gr.query();
var groups = [];
//Getting unique group details
while (gr.next()) {
    groups.push(gr.assignment_group.toString());
}

//Get Interaction Assignment group
var intGr = new GlideAggregate("interaction");
//Your actual filter in report
intGr.addQuery("active=true");
intGr.groupBy('assignment_group');
intGr.query();
while (intGr.next()) {
    groups.push(intGr.assignment_group.toString());
}

// Consolidated groups
var grpList = groups.join();
gs.print(grpList);

var userGr = new GlideAggregate("sys_user_grmember");
userGr.addEncodedQuery('groupIN' + grpList);
userGr.groupBy('user');
userGr.query();

var members = [];
while (userGr.next()) {
    members.push(userGr.user.toString());
}

gs.print("Members " + members.join());
for (var i = 0; i < members.length; i++) {

    gs.log('Manager: ' + members[i]);

    var scheduleReport = new GlideRecord('sysauto_report');
    scheduleReport.get('YOUR_SCHEDULE_REPORT_SYS_ID');
    scheduleReport.user_list = members[i];
    scheduleReport.run_as = members[i];
    scheduleReport.update();
    SncTriggerSynchronizer.executeNow(scheduleReport);
    gs.sleep(10000);
}

gs.log('Completed');

[Ozgur Baykal]

Thank you @Vasantharajan N . This works exactly we needed. I appreciate it.

[Wendy20]

Hi!

Thank you for sharing this. I tried this and it worked but then it started triggering the same report multiple times from different members of the group. Any idea why this might happen? also is there a way to make the "run as" on BCC instead of a random group member? 

I appreciate you taking your time to look into this.

Wendy

[sharan16]

This script is dynamically changing the recipients of the scheduled email,  but it is not filtering the report for each recipient only for their relevant data -> Do we have any solution to this?