Scheduling a report for dynamic Recipients based on the table column of the report

[Navneet Arora]

I have a below requirement:

1. Create a report for finding issues created in last 1 week 
2. Schedule the report and send the report only to the Assigned To: field recipients (dynamic)

For e.g. 3 issues were created in last 1 week and they were assigned to User A , User B , User C . So as per requirement , the report should be sent to only User A , User b and User C only i.e. dynamic recipients based on the value in assigned to field in the table selected for report.

As ,the report doesn't allow any dynamic user or fields as shown below, please suggest how I can implement my requirement to make it dynamic.

[Community Alums]

You can use scheduled job and fetch table record as report and send mail to assigned to field user.

Please refer :https://community.servicenow.com/community?id=community_question&sys_id=3d500fa1db98dbc01dcaf3231f961915 

Mark it as helpful, if it works .

[Sagar Pagar · Accepted solution]

Hello Navneet,

You need to schedule a report and get the assigned to users in "Users" list field of scheduled report. use the advanced option and use below script to update the dynamic users.

function lookupUsersAndUpdateReport(){

	var senderArray = [];

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

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

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

	var arrayUtil = new ArrayUtil();

	senderArray= arrayUtil.unique(senderArray);   // unique assigned to

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

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

	return false;
}

Note- The Orlando version or later version not support the report filter or current object in schedule report script. It is working in New york and earlier version.

In Orlando or newer version, you need another schedule script/scheduled job to update the users list.

var scheduleReport = new GlideRecord('sysauto_report');
scheduleReport.get('add sys_id here'); //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());
}

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

scheduleReport.user_list = finalRecipients.join(','); //get unique recipients
scheduleReport.update();

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

Thanks,

Sagar Pagar

[Navneet Arora]

Thank you so much Sagar, the second option worked for me. 🙂

[Sagar Pagar]

Good to know that it works for you.

Thanks,

Sagar Pagar