Dynamic Scheduled Report Recipients Based on Assignment Group Members and Record Ownership
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2 hours ago
Hi Community,
I am looking for feedback on a custom solution that we implemented to meet a reporting requirement.
Requirement
We needed scheduled reports to be sent daily to:
- Change Assignees who have matching records in the report.
- Assignment Group Managers / Group Members responsible for those records.
- Only users who have records matching the report filter should receive the report.
Example use case:
- Daily report for Abandoned Active Changes (Implement State).
- Email should only be sent to users who have matching records in the report.
- Avoid sending reports to users with no matching records.
script include
var dynamic_email_to_user = Class.create();
dynamic_email_to_user.prototype = {
initialize: function() {
},
getUserFromGroup: function(reportId,query,table)
{
var users = [];
// Get the distinct user from group member table related to the groups
if(this.checkData(query,table,gr.user) == false)
{
return '';
}
else{
var gr = new GlideAggregate("sys_user_grmember");
gr.addEncodedQuery('group=a3292444a527e100cd783922e1e94e95^ORgroup=c90748e9db5eaf806377cae43a96197f^ORgroup=8629e044a527e100cd783922e1e94eee^ORgroup=3fe217484f811bc00bb49a701310c779^ORgroup=6d8b99964fd603000bb49a701310c7c9');
gr.addAggregate("COUNT");
gr.groupBy("user");
gr.query();
while (gr.next()) {
var result = this.checkInReport(query,table,gr.user);
var userId='';
if(result == true)
{
userId = gr.user.sys_id.toString();
users.push(userId);
}
}
if(users.toString() == '') // Return true if records not assigned to anyone to bypass the scheduled job condition
{
return 'true';
}
else
return users.toString();
}
},
checkInReport: function(query,table,user)
{
var gr = new GlideRecord(table);
gr.addQuery('assigned_to',user);
gr.addEncodedQuery(query);
gr.query();
if(gr.next())
{
return true;
}
return false;
},
checkData: function(query,table,user)
{
var gr = new GlideRecord(table);
gr.addEncodedQuery(query);
gr.query();
if(gr.getRowCount() > 0)
{
return true;
}
return false;
},
runReport: function(SchReportId)
{
var reportUser='';
var param2 = {};
var gr = new GlideRecord('sysauto_report');
gr.addQuery('sys_id',SchReportId);
gr.addQuery('active',true);
gr.query();
if(gr.next())
{
var omitRecord = false;
var reportId = gr.report.sys_id.toString();
var reportCon = new GlideRecord('sys_report');
reportCon.get(reportId);
if(gr.omit_if_no_records == true)
omitRecord = 'true';
reportUser = this.getReportUsers(reportCon.filter.toString(),reportCon.table.toString(),omitRecord);
if(reportUser == '' && omitRecord == 'true')
{
return '';
}
if(gr.u_static_recipients == true)
{
reportUser += ','+gr.user_list.toString();
}
var subject = gr.report_title.toString();
param2.subject = subject;
param2.report = reportId;
if(reportUser.toString() != '')
gs.eventQueue('application.integration.weekly.report',gr,reportUser.toString(),JSON.stringify(param2));
}
},
getReportUsers: function(query,table,omitRecord)
{
var users = [];
var gr = new GlideAggregate(table);
gr.addEncodedQuery(query);
gr.addAggregate("COUNT");
gr.groupBy("assigned_to");
gr.query();
if(omitRecord == 'true')
{
if(!gr.hasNext())
{
return '';
}
}
while (gr.next()) {
var userId='';
userId = gr.assigned_to.sys_id.toString();
users.push(userId);
}
if(users.toString() == '') // Return true if records not assigned to anyone to bypass the scheduled job condition
{
return 'true';
}
else
return users;
},
type: 'dynamic_email_to_user'
};
Solution Implemented
We created a custom Script Include (dynamic_email_to_user) and configured the Scheduled Report to use Dynamic Recipients.
Has anyone implemented a similar pattern for dynamically distributing reports to assignees or assignment group managers?
please help me to achieve this requirment
if there is any mistake in my script plrz correct my script
@Ankur Bawiskar need your support
0 REPLIES 0