Reporting on the Latest Status – Part 1/3 on Conquer Challenging Reports by Leveraging the Now Platform

[Adam Stout]

This post is part one of a three-part series on how to leverage the Now Platform to easily solve otherwise tough reporting problems.  This is a followup to the Performance Analytics and Reporting Office Hours from 2/12/2020.  If you would like to hear me explain this, you can check out the recording and presentation here.

The Business Case

Beki, a Portfolio Manager in our Project Management Office, came to me and said:

I often find myself chasing project updates and it is such a mundane task. I must go into each project’s related details to determine the current status. I spend a couple of hours every week just clicking through all these projects. What I would give to have a better way and nudge folks to keep the project up to date...

Who wants to spend their time clicking through a list record by record and sending emails and making calls all the time? There must be something we can do for Beki to make the process flow better. Something to help us get better outcomes with less manual effort to free Beki up to focus on high-value activities.

This is ServiceNow, of course there is something we can do here. Let’s walk through how to make work, work better for Beki.

Before we get started

If you haven’t read it yet, I recommend you start by reading the post, “It's Just a "Reporting" Field...” which discusses why it is so important to build analytics into your application rather than just creating important reporting fields outside of your operational system.

It’s ok; I’ll wait. Go ahead and read it. It is short. Here is the link again: “It's Just a "Reporting" Field...”

OK, now that you are back, let’s talk about how to make this work.

Leveraging the Now Platform

Before we get too far, you need to have a working understanding of the Now Platform. If you haven’t done so, I recommend the ServiceNow Fundamentals instructor lead (3-day) class. I prefer the instructor-led, but if that isn’t an option, you can take the ServiceNow Fundamentals self-paced version. [As a bonus, if you complete the self-paced version before the end of March 2020, it looks like they are offering a free voucher to take the ServiceNow Certified System Administrator (CSA) exam]. Here is some information comparing the different versions available to you.

If you are considering taking the Performance Analytics Certified Application Specialist exam, you’ll see the ServiceNow Fundamentals is the first step in the learning path.

The better you understand the Now Platform, the easier your life will be working with Performance Analytics and Reporting in ServiceNow. Analytics is not separate; it is part of the Now Platform.

Use Case Specifics

Now that we know the why and what, let’s walk through a specific use case...

On the Project table (pm_project) there is a one to many relationship with Status Reports (project_status). Every week, a status report is (or at least should be) submitted. We need to report on easily:

• Projects that have not had a status report submitted in the last week
• Projects that have not had a status report submitted at all
• Projects, including the date of the latest status report as well as including fields from the latest status report

Proposed Solution

Add a field to the base table and a Business Rule on the child to keep the new field up to date. By adding this new field, we can easily report on the information contained in the latest status report along when reporting on the project table itself without having to worry about missing status reports or multiple status reports.

What Else We Get

Now that we have this data easy to access, we can create Performance Analytics Indicators on project, including the latest status report information. Adding a breakdown by latest overall status is interesting as well.

In addition to Reporting and Performance Analytics, we can drive the desired action we have as well. For instance, adding a warning to the project that you need to file a status report.

To track how our processes are improving over time, we can add a KPI for project managers tracking how well they are keeping their projects up to date and to help the project managers add a KPI that can alert them that certain projects have not had a timely update.

We can also now use the latest project status to build interactive filters on the project.

Solution Walk Through

Create a new field

On the Project table (pm_project), create a new field to hold the reference to the latest Status Report.

You can name this field anything that makes sense in your organization. Typically, I use “Latest Status Report”. I recommend that you make this read-only so it is only populated via the business rule that we will be creating in the next step. Be sure to remember what the physical name of this field is (u_latest_status_report in this example).

Add Business Rule

Next, we add a Business Rule to the Status Report table (project_status) so that every time a status report is entered or (#1) the project or date changes, we update the appropriate Project to match.

 

Be sure to set this to run after the status report is saved to the database (async may be OK here too). We also need to be sure to run this on (#2) Insert/Update/Delete to cover all the cases when we would need to update the Project.

The “Advanced” tab (#3) is where the magic happens.

This logic should probably live in a Script Include instead of in the Business Rule, but for this example, I put it here directly. Wherever you put it, be sure to create a test for this in the Automated Test Framework to ensure this continues to work going forward.

Here is some example logic to get you started, and as with all examples, be sure to test that this meets your needs prior to deploying this in a production environment.

(function executeRule(current, previous /*null when async*/) { 
var getLatestStatusReport = function (project) 
{ 
var lastStatus = new GlideRecord('project_status'); 
lastStatus.addQuery('project', '=', project.getValue('sys_id')); 
lastStatus.orderByDesc('as_on'); 
lastStatus.setLimit(1); 
lastStatus.query(); 
if(lastStatus.next()) 
{ 
return lastStatus; 
} else { 
return false; 
} 
}; 
var setLatestStatusReport = function (project) 
{ 
var proj = new GlideRecord('pm_project'); 
proj.get(project.getValue('sys_id')); 
var statusReport = getLatestStatusReport(project); 
if(statusReport == false) 
{ 
gs.info('No status report found for ' + project.getDisplayValue()); 
return; 
} 
gs.info('Status report found for ' + project.getDisplayValue() + ' - ' + statusReport.as_on); 
proj.setValue('u_latest_status_report', statusReport.getValue('sys_id')); 
// turn off the audit, just update the status report 
proj.setWorkflow(false); 
proj.autoSysFields(false); 
proj.setEngines(false); 
proj.update(); 
return; 
}; 
gs.addInfoMessage('Latest status report updated on current project: ' + current.project.getDisplayValue()); 
setLatestStatusReport(current.project); 
if(current.project != previous.project && !gs.nil(previous.project)) 
{ 
gs.addInfoMessage('Latest status report updated on previous project: ' + previous.project.getDisplayValue()); 
setLatestStatusReport(previous.project); 
} 
})(current, previous);

Run Fix Script

We are all set up to populate the data going forward, but we need to fix all the existing Projects. We need to run a Fix Script to find the latest status reports and update all the existing projects to which they belong. If we used a script include (HINT!) in the BR, this would be easier, but we’ll copy and paste for this example.

var getLatestStatusReport = function (project) 
{ 
var lastStatus = new GlideRecord('project_status'); 
lastStatus.addQuery('project', '=', project.getValue('sys_id')); 
lastStatus.orderByDesc('as_on'); 
lastStatus.setLimit(1); 
lastStatus.query(); 
if(lastStatus.next()) 
{ 
return lastStatus; 
} else { 
return false; 
} 
}; 
var setLatestStatusReport = function (project) 
{ 
var proj = new GlideRecord('pm_project'); 
proj.get(project.getValue('sys_id')); 
var statusReport = getLatestStatusReport(project); 
if(statusReport == false) 
{ 
gs.info('No status report found for ' + project.getDisplayValue()); 
return; 
} 
gs.info('Status report found for ' + project.getDisplayValue() + ' - ' + statusReport.as_on); 
proj.setValue('u_latest_status_report', statusReport.getValue('sys_id')); 
// turn off the audit, just update the status report 
proj.setWorkflow(false); 
proj.autoSysFields(false); 
proj.setEngines(false); 
proj.update(); 
return; 
}; 
var getAllProjectsWithStatus = function () 
{ 
var proj = new GlideRecord('pm_project'); 
proj.addEncodedQuery('RLQUERYproject_status.project,>=1^ENDRLQUERY'); 
proj.query(); 
return proj; 
}; 
var project = getAllProjectsWithStatus(); 
gs.info(project.getRowCount() + ' projects to update'); 
while(project.next()) 
{ 
setLatestStatusReport(project); 
} 
gs.info('Update complete');

Work is Better

Now Beki can use her dashboard to quick browse projects based on the Overall Status from their latest status report. She can see when the last status report was submitted as well as seeing which projects are missing their status reports completely.

Using a report as an Interactive Filter makes it easy to review and focus on the problem projects.

I’m pretty sure Beki is going to buy me a donut once she sees this.

Other Use Cases

This same technique can be used wherever you have a one to many relationships. I commonly see it used when we have a specific child record we need want to report on. In this case, the latest status report but it could just as easily be the “active task”. You need to be able to set the logic to pick the appropriate task.

This technique also works to summarize the child records. For instance, you could add some integer fields to count the number of Problem Tasks (total and open) for a Problem record if this was something that you need to quickly report on and wanted to integrate in the workflow for Problems.

Wrapping Up

Analytics are an integral part of the Now Platform. Do not restrict yourself to just Reporting or just Performance Analytics! Use the Now Platform to get the most out of your ServiceNow investment and optimize your workflow.

With a small amount of effort (this should have taken you less than 15 minutes to do), you now have greatly expanded your analytics capabilities with a solution you can use across the platform, not just in a one-off report.

Up Next

In the next installment of this series, we’ll cover reporting on prefixes and other substrings. Check back next week to learn more about how to Conquer Challenging Reports by Leveraging the Now Platform.