I have been asked to report on survey results group by Assignment group and assigned to related to each assignment group manager. Since you cannot do multiple group by in reports I thought I would create a notification that would go to each assignment group manager. I got it to work but they don't like that the incident number, assignment group and assigned to repeat (screenshot below). I'm at a loss on how to get do this or if there is a better way. They want a way that they get contents that display the group then the assigned to the incident number and then the results for that incident. Where assigned to and incident number only display once for each result where they are different. So something like below.
Group name
Assigned to
Incident#
results
incienet#
results
assigned to
Incident#
results
etc.
This is the screenshot of the notification before they saw it and said they didn't want repeating value.
This is the code I used in my notification mail script
(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
/* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
/* Optional GlideRecord */ event) {
// Add your code here
var gr = new GlideRecord ("asmt_metric_result");
gr.addEncodedQuery("metric.category=1573fa38db18f15053c0231c13961913^instance.task_id.assignment_group.manager=" + event.parm1);
gr.groupby("instance.task_id.assigned_to");
gr.groupby("instance.task_id");
gr.orderBy('instance.task_id.number');
gr.query();
template.print("<table border=1 boardercolor=black style='font-family:Arial; font-size:16px;'>");
template.print("<tr><th align='left'>Incident Number</th><th align='left'>Assignment Group</th><th align='left'>Assigned to</th><th align='left'>Question</th><th align='left'>Value</th></tr>");
while (gr.next()){
template.print("<tr><td>" + createLinkForObject('incident', gr.instance.task_id, gr.instance.task_id.number) + "</td><td>" + gr.instance.task_id.assignment_group.getDisplayValue() + "</td><td>" + gr.instance.task_id.assigned_to.getDisplayValue() + "</td><td>" + gr.metric.question + "</td><td>" + checkValue(gr.actual_value, gr) + "</td></tr>");
}
template.print("</table>");
})(current, template, email, email_action, event);
function checkValue(value, record){
if (value == -1){
return record.string_value;
}
else{
return value;
}
}
function createLinkForObject(strTableName, strSysID, strLabel) {
return '<a href="' + gs.getProperty('glide.servlet.uri') + 'nav_to.do?uri=' + gs.generateURL(strTableName, strSysID) + '">' + strLabel + '</a>';
}
It alsmost sounds to me like they are asking for avreage for the result back, not on each individual question, you can probably create a report for it.
No that is not what they wanted. The does not give you survey results for each user in each group. I got it working with an email script. They wanted all the results for each assigned to including text results. They just didn't what to see that users name or incident number repeated multiple times for each result.
Now it looks like this:
