Dynamic Scheduled Report Recipients Based on Assignment Group Members and Record Ownership

nameisnani
Giga Sage

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'
	};

 

nameisnani_0-1794889679982.png

 

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