A few months ago, I wrote about how to embed report charts into the body of an email. This covered many report types but notably, left out list reports. In this follow-up, we’ll review how to embed list reports into the body of an email.
By default, ServiceNow attaches report lists (as a PDF, CSV, XLSX, etc.) to emails when emailing a scheduled report. This requires the recipient to open the email, then open the attachment. This experience does not always meet the users’ expectations.
With the power of the Now Platform, you can apply a small customization to embed the list into the body of the email to give you something like this for your users:
NOTE: To implement this, you need to have admin access and as with all customization, this should first be done in a sub-prod environment and thoroughly tested in your environment prior to deploying to your production instance.
Create Business Rule to Embed List
Create a new business rule on sys_email to modify the content of emails to embed the list before the record is first inserted.
Fill out the form with the following values:
- Name: Inline Report List
- Table: Email [sys_email]
- Active: checked
- Advanced: checked
- When to run
-
- When: before
- Insert: checked
- Filter Conditions
-
- Target table [is] sysauto_report
- Advanced Section - Script:
(function executeRule(current, previous /*null when async*/) {
// check if tag is found in body
if(current.body.indexOf('[embedded list]') == -1)
{
// if not found, leave everything alone
//gs.warn('embedded list text not found');
return;
}
var getReportFromEmail = function (scheduledReportSysID)
{
var scheduledReport = new GlideRecord('sysauto_report');
scheduledReport.addQuery('sys_id', '=', scheduledReportSysID);
scheduledReport.query();
if(scheduledReport.next())
{
return scheduledReport.getValue('report');
}
gs.error('No scheduled report found');
return false;
};
// Important fields from the report - table, filter, field_list, field
var getReportMetaData = function (report_sys_id)
{
var rep = new GlideRecord('sys_report');
rep.get(report_sys_id);
return rep;
};
var getReportTotalRows = function (rep)
{
// create a glide object for the table
var data = GlideAggregate(rep.getValue('table'));
if(!data.isValid())
{
gs.error('Invalid reporting table');
return false;
}
data.addAggregate('COUNT');
// need to add support for report sources
if(rep.getValue('report_source'))
{
data.addEncodedQuery(rep.getElement('report_source').getRefRecord().getValue('filter'));
}
// add report filter
data.addEncodedQuery(rep.getValue('filter'));
data.query();
gs.error(data.getEncodedQuery());
if(data.next())
{
return Number(data.getAggregate('COUNT'));
}
return null;
};
// get the data that the report also gets
var getReportData = function (rep)
{
// create a glide object for the table
var data = GlideRecordSecure(rep.getValue('table'));
if(!data.isValid())
{
gs.error('Invalid reporting table');
return false;
}
// need to add support for report sources
if(rep.getValue('report_source'))
{
data.addEncodedQuery(rep.getElement('report_source').getRefRecord().getValue('filter'));
}
// add report filter
data.addEncodedQuery(rep.getValue('filter'));
data.setLimit(gs.getProperty('reporting.inline.list.row.limit', '100')); // set a max number of rows
gs.error(data.getEncodedQuery());
data.query();
return data;
};
var getLink = function (element)
{
if(element.getED().getInternalType() == 'collection')
{
return gs.getProperty('glide.servlet.uri') + element.getLink(true);
} else {
return gs.getProperty('glide.servlet.uri') + element.getRefRecord().getLink(true);
}
};
var getReportLink = function (report)
{
return gs.getProperty('glide.servlet.uri') + 'sys_report_template.do?jvar_report_id=' + report.getValue('sys_id');
};
var formatHTMLTableFromArray = function (data, report)
{
// Change the font size or use the system default
var myFontSize = gs.getProperty('reporting.inline.list.style.font-size', gs.getProperty('css.base.font-size', '10pt'));
// Change the font family or use the system default
var myFontFamily = gs.getProperty('reporting.inline.list.style.font-family', gs.getProperty('css.base.font-family', '"Trebuchet MS"'));
// Change the color for the header row or use the system default
var myHeaderRowColor = gs.getProperty('reporting.inline.list.style.header.background-color', gs.getProperty('css.base.color', '#81b5a1'));
// Change the color for the fonts in the header row or use the system default
var myHeaderRowFontColor = gs.getProperty('reporting.inline.list.style.header.color', 'white');
// Change the color for even rows or use the system default
var myEvenRowColor = gs.getProperty('reporting.inline.list.style.odd.color', '#f7f7f7');
// Change the color when hovering over a row or use the default
var myHoverColor = gs.getProperty('reporting.inline.list.style.hover.color', '#c0c2c4');
// Change the font, color, etc for the warning message or use the system default
var myWarningMessage = gs.getProperty('reporting.inline.list.style.warning.style', 'font-weight: bold; color: #fc726d;'); // shoudl we do these all as style blocks?
// this would be a good place to override these values in the future with report os scheduled report specific settings
var style = "<style>";
style += "#inlineTable {border-collapse: collapse;width: 100%;";
style += "font-size: " + myFontSize + ";font-family:" + myFontFamily + ";}";
style += "#inlineTable th, #inlineTable td {border: 1px solid #ddd;padding: 8px;}";
style += "#inlineTable th {padding-top: 12px;padding-bottom: 12px;text-align: left;background-color: " + myHeaderRowColor + "; color: " + myHeaderRowFontColor + ";}";
style += "#inlineTable .even {background-color: " + myEvenRowColor + ";}";
style += "#inlineTable tr:hover {background-color: " + myHoverColor + ";}";
style += "#warningMessage {" + myWarningMessage + "}";
style += "</style>";
var html = style;
if(data.stats.totalRows > data.stats.returnedRows)
{
html += '<div id="warningMessage">Warning: This table is only displaying ' + data.stats.returnedRows + ' of ' + data.stats.totalRows + '. Please see the attachment or view the report <a href="' + getReportLink(report) + '">here</a> to get the full information.</div>';
}
html += '<table id="inlineTable">';
for(var r = 0; r < data.rows.length; r++)
{
var cellDivider = 'td';
var rowStyle = 'odd';
if(r % 2 == 0)
{
rowStyle = 'even';
}
if(r == 0)
{
cellDivider = 'th';
rowStyle = '';
}
html += '<tr class="' + rowStyle + '">';
html += '<' + cellDivider + '>' + data.rows[r].join('</' + cellDivider + '><' + cellDivider + '>') + '</' + cellDivider + '>';
html += '</tr>';
}
html += '</table>';
html += 'View this report with live data at <a href="' + getReportLink(report) + '">' + getReportLink(report) + '</a>.';
return html;
};
var scheduledReport = current.getValue('instance');
var repSysID = getReportFromEmail(scheduledReport);
var repInfo = getReportMetaData(repSysID);
if(repInfo.getValue('type') != 'list')
{
// not a list report, nothing to do here
//gs.error('not a list report (it is a ' + repInfo.getInfo('type') + '), nothing to do here');
return;
}
var data = getReportData(repInfo);
var fields = repInfo.getValue('field_list').split(',');
// all processed data
var processedData = {stats: {totalRows: getReportTotalRows(repInfo), returnedRows: data.getRowCount()}, rows: []};
while(data.next())
{
// process the headers if available
if(data.getLocation() == 0)
{
var headers = [];
for(var f = 0; f < fields.length; f++)
{
headers.push(data.getElement(fields[f]).getLabel());
}
processedData.rows.push(headers);
}
var row = [];
for(var g = 0; g < fields.length; g++)
{
var el = data.getElement(fields[g]);
var value = el.getHTMLValue(); //getDisplayValue();
// always include link on first column
if(g == 0)
{
value = '<a href="' + getLink(data) + '">' + value + '</a>';
} else {
var type = el.getED().getInternalType();
// format different types differently
if(type == 'reference')
{
value = '<a href="' + getLink(el) + '">' + value + '</a>';
}
}
row.push(value);
}
processedData.rows.push(row);
}
// replace the tag in quotes since that can't have tags added to it. This should be a smarter regex, but we'll brute force it for now
current.body = current.body.replace('\'[embedded list]\'', '');
current.body = current.body.replace('"[embedded list]"', '');
// add the image tags
current.body = current.body.replace('[embedded list]', formatHTMLTableFromArray(processedData, repInfo));
// should we remove the attachment? perhaps this would be a good option to add
return;
})(current, previous);
On a scheduled report, specify where to put the list
Use the placeholder text “[embedded list]” in the Introductory message. It is recommended to make this text a link back to the desired report or dashboard to drive engagement with the live data in your instance.
The original attachment will still be there.
Testing
When testing in your sub-production environment, you can run the report by opening it from the “Reports > Scheduled Reports” list and clicking “Execute Now”. Then navigate to “System Logs > Emails” to see the email and preview it.
Important Notes About the Script
Size Limit
I created a property to limit the size of the table in the email. I’m defaulting to 100 rows in the email but if I set the system property “reporting.inline.list.row.limit”, I can change this without changing the code.
If the report will return more than this limit, this message gets added:
Style Options
There are several system properties that control the styling of the email including:
- reporting.inline.list.style.font-size
- reporting.inline.list.style.font-family
- reporting.inline.list.style.header.background-color
- reporting.inline.list.style.header.color
- reporting.inline.list.style.odd.color
- reporting.inline.list.style.hover.color
- reporting.inline.list.style.warning.style
These all have defaults, but you can set them as system properties to modify them without having to change the Business Rule.
Ideas for Additional Functionality
This script is functional but there are some areas where we could add even more to it. Here are some ideas for you to expand on:
Configure Style
We could add some configuration fields to the Scheduled Report record and read them in formatHTMLTableFromArray to override the system properties we are getting. This would allow the users to configure how the email looks for each report.
Option to Delete Attachment
Since we are including the information in the email itself, do we need to include the attachment? Perhaps we should have an option to remove it.
Multiple Reports
Currently, this does not support sending multiple reports in the same email, unlike the embedded charts email script which does.
Additional Documentation
- Schedule a report -https://docs.servicenow.com/bundle/orlando-platform-administration/page/administer/reference-pages/t...
- Automate report distribution - https://docs.servicenow.com/bundle/orlando-performance-analytics-and-reporting/page/use/reporting/ta...
- Create a business rule - https://docs.servicenow.com/bundle/orlando-application-development/page/script/business-rules/task/t...
- System Properties - https://docs.servicenow.com/bundle/orlando-platform-administration/page/administer/reference-pages/t...
What's Next?
We have gone over how to in-line a list report into the scheduled report email, what else can we do with this? What ideas do you have to expand the functionality of Scheduled Reports? Share them here and add them to the Idea Portal as well to help influence the future direction of Now Intelligence.
57 Comments
