This post is part three 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
Peter, a Demand Manager, has been busy reviewing incoming ideas. One of the key pieces of information needed to triage the idea is the business capability related to the idea. This is where the problem lies.
It is quick for users to add their ideas to the system. A couple of clicks and I get everything I need. The submitter is quickly able to set the Business Capabilities that the idea impacts, but when I try to report on this, I’m not getting what I want.
I want to group by capability, but when I do, I get all the combinations, not just a clean report with a count for each capability. HELP!!!
Before we get started
This is part three of the series, so go back and read part one and part two if you haven’t. You’ll also want to be sure to have had some training on the Now Platform. [That is also discussed in part 1, so get the links there.]
Use Case Specifics
We need to be able to group by and filter by individual fields in the list field. List fields contain a comma-separated list of sys_ids (or choice values if it is a choice list). The platform sees this as a string, and the Report Designer won’t let you group by it (although you can use it as an additional group by field). If you do select the field as a group by, order matters, and each combination is group separately, which is rarely what you want to see.
Proposed Solution
To make this easily reportable and to support dot-walking through the values in the list, we will create a new table that will be populated via a Business Rule when the list changes.
Solution Walk Through
All code examples are provided to get you started. Prior to being deployed in your environment, they should be reviewed for appropriateness and thoroughly tested to meet your needs.
Create a new table
The secret to making this work is creating a table that will store a reference to the original Idea and the table we are referencing. We only need two fields to make this work.
Here we can see the u_business_capability field, which references Business Capability and u_idea, which references Idea.
Going forward, we’ll report on this table when we need to report on the Business Capabilities for Ideas.
Add Business Rule
To make this work smoothly, we’ll use a Business Rule on the idea table that executes when the Business Capabilities list is updated. The Business rule will create a row for each value in the list field.
Here is the Business Rule I created to do this:
A few key points:
- We need to use the “Advanced” settings.
- We want to initial update to Idea to happen before we do this, so we will run after the update (this could be made async in needed).
- We want this to run on Insert, Update, and Delete. [Don’t forget to select Delete!!!! We need to remove records we don’t need if the Idea is removed.]
- We only need this to run if the Business Capabilities change.
Now that we have that configured, we need to add the script logic.
(function executeRule(current, previous /*null when async*/) {
var createShadowRecords = function (idea, capabilities)
{
// if there are cabilities, add them
if(!gs.nil(capabilities))
{
var capObj = capabilities.split(',');
for(var i = 0; i < capObj.length; i++)
{
var cap = new GlideRecord('u_idea_business_capability');
cap.addQuery('u_idea', '=', idea.getValue('sys_id'));
cap.addQuery('u_business_capability', '=', capObj[i]);
cap.query();
// if they are missing add them, else do nothing
if(!cap.hasNext())
{
cap.initialize();
cap.setValue('u_idea', idea.getValue('sys_id'));
cap.setValue('u_business_capability', capObj[i]);
cap.insert();
}
}
}
// remove any capabilities that are there any more
var removeCap = new GlideRecord('u_idea_business_capability');
removeCap.addQuery('u_idea', '=', idea.getValue('sys_id'));
if(!gs.nil(capabilities))
{
removeCap.addQuery('u_business_capability', 'NOT IN', capabilities);
}
removeCap.deleteMultiple();
};
createShadowRecords(current, current.business_capabilities);
if(gs.nil(current) && !gs.nil(previous))
{
createShadowRecords(previous, '');
}
})(current, previous);Run Fix Script
We are all set going forward, but we need to fix the data that is already there. A quick fix script should do the trick.
var createShadowRecords = function (idea, capabilities)
{
// if there are cabilities, add them
if(!gs.nil(capabilities))
{
var capObj = capabilities.split(',');
for(var i = 0; i < capObj.length; i++)
{
var cap = new GlideRecord('u_idea_business_capability');
cap.addQuery('u_idea', '=', idea.getValue('sys_id'));
cap.addQuery('u_business_capability', '=', capObj[i]);
cap.query();
// if they are missing add them, else do nothing
if(!cap.hasNext())
{
cap.initialize();
cap.setValue('u_idea', idea.getValue('sys_id'));
cap.setValue('u_business_capability', capObj[i]);
cap.insert();
}
}
}
// remove any capabilities that are there any more
var removeCap = new GlideRecord('u_idea_business_capability');
removeCap.addQuery('u_idea', '=', idea.getValue('sys_id'));
if(!gs.nil(capabilities))
{
removeCap.addQuery('u_business_capability', 'NOT IN', capabilities);
}
removeCap.deleteMultiple();
};
var getAllIdeasWithCapabilities = function ()
{
var idea = new GlideRecord('idea');
idea.addNotNullQuery('business_capabilities');
idea.query();
return idea;
};
// get all the existing ideas with capabilties
var idea = getAllIdeasWithCapabilities();
gs.info(idea.getRowCount() + ' ideas to process');
while(idea.next())
{
gs.info(idea.getDisplayValue() + ' - ' + idea.business_capabilities);
createShadowRecords(idea, idea.business_capabilities);
}
gs.info('Update complete');Work is Better
Peter can now easily report on the Business Capabilities of Ideas. In addition, he can group by the Business Capability and can create an interactive filter on it. Peter is now happy.
Important Note: This is NOT needed to support Performance Analytics. In Performance Analytics, you can breakdown by a list just by selecting it (in this case with a Breakdown Source on Business Capabilities).
Other Use Cases
Any place you have a list, this will work.
Wrapping Up
The Now Platform is integral to Analytics. 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.
This is the last installment of this series. We went over how to leverage the Now Platform to report on:
- Child Tables – The “Latest Status” report example
- Substrings – Pulling out information from the short description
- Lists – What you just finished reading
Now that you have seen some real examples and had a chance to try them out, leave some comments about the use cases you are solving with this technique.
15 Comments
