I've been wanting to write about this for some time now and finally just took the time to actually do it. The reporting engine is easy to work with and allows users to create simple reports very easily. What if you want to include a couple different queries, based on a date/time field for instance, in your report? Get data from different tables? Not so easy anymore, unfortunately.
I've been using Email Notifications as a way to create custom reports for a while now. It's a fairly simple way to deliver data in a format that you have complete control over and is often overlooked. The output can be quite simple or very elaborate, with multiple colors to highlight high priority items, different typefaces and sizes, etc... You have the full power of HTML at your fingertips. The simple example I'm going to build is for a custom Incident report.
Step 1, create an Event that will trigger the Email Notification:
Step 2, create a Scheduled Script Execution record that will raise the event. Select the System Definition \ Scheduled Jobs module and click on the "New" button at the top of the list. Select "Automatically run a script of your choosing" on the Interceptor page:
...and then create the following record:
The script will run everyday at 7:00 am, and if it is Monday - Friday, will raise the event we created. The event will trigger the email notification we will create next:
Select "Event is fired" in the "Send when" drop down and select the new event we created in the "Event name" field. I have not set any conditions in this example, but you could, for example, perform a check to see if any records meet your criteria before creating the report. I prefer to add a message in the report itself to signify no records were found in order to avoid any confusion - it really depends on the audience and content of the report.
You will want to select the "Send to event creator" check box on the "Who will receive" tab/section while you are testing this out to make sure you receive the emails, otherwise you will never get them and end up blaming me. You may have to select the "Advanced view" Related Link in order to see it.
The secret to the solution is found in the "Message" field on the "What it will contain" tab/section. This is where we use some mail_script code to get the data and output it in the format we want:
The following active Incidents were created at least 7 days ago:
<mail_script>
baseUrl = gs.getProperty("glide.servlet.uri");
var gr = new GlideRecord("incident");
gr.addEncodedQuery("active=true^sys_created_onBETWEENjavascript:gs.daysAgoEnd(13)@javascript:gs.daysAgoStart(7)");
gr.orderBy('sys_created_on');
gr.query();
if (gr.hasNext()) {
template.print("<br/><br/><br/>Within 7 - 13 days:<br/>");
while (gr.next()) {
template.print(gr.getDisplayValue('sys_created_on') + " - <a href='" + baseUrl + gr.getLink() + "'>" + gr.getValue('number') + "</a> - " + gr.short_description + "<br/>");
}
}
var gr = new GlideRecord("incident");
gr.addEncodedQuery("active=true^sys_created_onBETWEENjavascript:gs.daysAgoEnd(29)@javascript:gs.daysAgoStart(14)");
gr.orderBy('sys_created_on');
gr.query();
if (gr.hasNext()) {
template.print("<br/><br/><br/>Within 14 - 29 days:<br/>");
while (gr.next()) {
template.print(gr.getDisplayValue('sys_created_on') + " - <a href='" + baseUrl + gr.getLink() + "'>" + gr.getValue('number') + "</a> - " + gr.short_description + "<br/>");
}
}
var gr = new GlideRecord("incident");
gr.addEncodedQuery("active=true^sys_created_onBETWEENjavascript:gs.daysAgoEnd(59)@javascript:gs.daysAgoStart(30)");
gr.orderBy('sys_created_on');
gr.query();
if (gr.hasNext()) {
template.print("<br/><br/><br/>Within 30 - 59 days:<br/>");
while (gr.next()) {
template.print(gr.getDisplayValue('sys_created_on') + " - <a href='" + baseUrl + gr.getLink() + "'>" + gr.getValue('number') + "</a> - " + gr.short_description + "<br/>");
}
}
var gr = new GlideRecord("incident");
gr.addEncodedQuery("active=true^sys_created_on<javascript:gs.daysAgoStart(60)");
gr.orderBy('sys_created_on');
gr.query();
if (gr.hasNext()) {
template.print("<br/><br/><br/>60 days or more:<br/>");
while (gr.next()) {
template.print(gr.getDisplayValue('sys_created_on') + " - <a href='" + baseUrl + gr.getLink() + "'>" + gr.getValue('number') + "</a> - " + gr.short_description + "<br/>");
}
}
</mail_script>
UPDATED December 20, 2017: I've removed the "gr.setLimit(x)" lines in the script as they were originally in there just for testing purposes. They were limiting the number of records returned by the query.
The script performs a few queries and outputs the results, grouping the results based on the creation date:
Here's another example:
Not fancy, but gets the job done. All self-contained within the email without any unnecessary attachments.
The examples I've shown are pretty basic, but I've created other reports that gather data from over a dozen tables and outputs the results in nice HTML tables, color-coding certain data when required. Makes for a real nice looking and effective report, all within the same platform. Easy to setup, can be tricky getting the proper format for your output, but worthwhile taking a look at, I believe.
