The discussion below is intended for advanced Reporting users who have 6 or months experience with Reporting and the same or more experience with the ServiceNow Platform. The advanced topics covered assume that you understand the basics of Reporting and have a working knowledge of JavaScript.
Introduction
ServiceNow has an amazing report builder that lets normal users quickly and safely perform complex queries leveraging the relationships in the system. With the addition of Related List Conditions, we are able to perform even more complex queries with a simple UI.
In addition to what we can do with the standard condition builder, we can leverage the power of the NOW Platform when we need to use even more complex logic.
We are going to discuss what to do in those situations. We are going to reach deep into the power of the NOW Platform and show you how to leverage that to answer those tough questions and make life easier for you and your users.
These techniques are demonstrated with Reporting but they work on list views and in Performance Analytics Indicator Sources and Breakdown Sources as well (really anywhere you have a condition in the NOW Platform).
Example Use Cases
What is a "complex" query? In this discussion, we'll focus on those queries that are not filtering on just the fields in the table we are querying or fields we can dot-walk to. Specifically, we will look at cases where we need to filter based on child records (in related lists) or any other data not normally accessible in the standard condition builder.
Here are some examples of queries that require more than the standard condition builder.
|
Use Case |
Approaches |
|
CIs with more than 1 active incident |
Related List Condition * |
|
CIs with no outages last month |
Related List Condition * |
|
Active Incidents with No Active Tasks |
Related List Condition * |
|
Incidents assigned to a manager |
Related List Condition * / Scripted Filter + Dynamic Filter Option |
|
Users in my assignment groups with no active incidents assigned to them |
Scripted Filter + Dynamic Filter Option |
* In pre-Istanbul instances can use dynamic filters for this use-case.
Anything you can do with a Related List Condition, we can do with Filter Scripted. However, the Related List Condition requires no customization and can be easily adjusted. If we can solve the problem with a Related List condition, we should.
Now that we know what we are talking about, let's talk about how to answer them.
Query Techniques
Before we walk through how to answer these, let's review what we have in our toolbox. These can be used anywhere the condition builder is available including list views, reports, indicator sources, and breakdown sources.
Related List Conditions cannot be edited on list views without List v3 enabled but they are available in the Report Designer with or without List V3.
Related List Conditions
Introduced for Indicator Sources and Breakdown Sources in Istanbul, related list conditions allow you to add filters based on records that reference the base record.
|
Pros |
Cons |
|
No development needed |
Only one related list condition per query |
|
Leverages reference defined in the instance |
Requires that references be defined |
|
Configured when you design the report |
See the documentation on Related List Conditions for more detailed information.
Scripted Filters and Dynamic Filter Options
By creating a script include you can use any logic supported by ServiceNow to filter your data. Once your logic is defined, you can call it via the following techniques.
Scripted Filter
A Scripted Filters allows you to tap into the power of the NOW Platform. Scripted Filters are implemented via a Script Include.
|
Pros |
Cons |
|
Encapsulates logic in a controlled manner |
Requires development |
|
Can accept parameters |
Requires knowledge of script include to be called |
| Requires user to type in the JavaScript correctly |
See the documentation on Scripted Filters for more detailed information.
Dynamic Filter Options
Once you have your Scripted Filter defined in a Script Include, you can then register a function as a Dynamic Filter Option and expose that logic to the users that need it (be sure that pa_admins have access to configure indicator sources and breakdown sources). Once it is defined, users (that you allow) can access this logic in the standard condition builder.
Once configured, Dynamic Filters can be called via the "is (dynamic)" operator.
|
Pros |
Cons |
|
Encapsulates logic in a controlled manner |
Requires development |
| Does not accept parameters |
Dynamic Filter Options are not limited to a one to one relationship with the Script Include functions. You can call the same Scripted Filter with different parameters as different Dynamic Filter Options. For example, in the use case above "Incidents on Cis that have at least one outage in the last 366 days", we could use the same function and have it take a parameter of the number of days and have two Dynamic Filter Options, one for 366 days and one for 30 days.
See the documentation on Dynamic Filter Options and Scripted Filters for more detailed information.
In-Line JavaScript
If you are calling a Script Filter with parameters or if you are calling your filter on a non-reference field, your Scripted Filter (as well as limited JavaScript) can be called in-line in the condition builder using the "is" operator. While this can be done with no development, the functionality is limited and use of this technique creates support and maintenance challenges.
From Jakarta onward, "while" loops are not supported in the Sandbox scope in which In-Line JavaScript is executed.
|
Pros |
Cons |
|
No development needed |
Limited functionality due to sandboxed scoped |
| Exposed logic that must be maintained in every instance where it is used | |
| Requires user to type in the JavaScript correctly | |
| Requires knowledge of JavaScript |
* If calling JavaScript functions directly without using a Scripted Filter
You may see this in older ServiceNow examples, but with the introduction of Dynamic Filter Options in Dublin, this technique should be avoided if at all possible.
Use Case Walk-Throughs
Now it is time to get our hands dirty. Let's go through some possible solutions for the use cases we introduced above.
Before executing any script you should thoroughly test it in your environment. Scripts are very powerful and you must ensure that you understand what you are running before you run it. The scripts below are intended for discussion purposes only.
Use Case: CIs with more than 1 active incident
Without Related List Conditions, we could query all incidents and group by the CI (perhaps with a pivot). This can be time-consuming and requires us to scan all the rows to find what we are looking for. Related List Conditions allow us to have ServiceNow do the hard work for us.
- Set the table that you want to report on (cmdb_ci)
- Expand the Related List Condition section
- Set the Related List Condition you want to traverse (Incident -> Configuration Item)
- Set the count you want the filter based on, for example:
- 1, for 1 or more incidents
- 5, for 5 or more incidents
- None, to get Cis with no related incidents
- Set the filter for the incidents that should be counted. We are filtering for only active incidents.
Use Case: CIs with no outages last month
Here is a slight modification of our example above. The key difference is that we want Cis that have no outages last month. Without Related List Conditions, this would require some custom scripting or exporting data and manually process it offline. Now it is a simple report.
Use Case: Active Incidents with No Active Tasks
To connect to a report that you may want on a user's dashboard, we can take a variant of the example above and use a dynamic filter to make it relevant in my (the current logged in user's) day to day work.
- Use the "is (dynamic)" operator to allow the same report to customize itself to each user
- Set the Related List Condition to find "No" qualifying records
- Set the conditions for qualifying incidents (we are looking for No Active Incidents)
Use Case: Incidents assigned to a manager
If you have a flag on your user table which marks managers vs. non-managers, this can be done easily with a related list condition. For this example, we are assuming we do not have such a flag.
First, we need a scripted filter to identify who is a manager and who is not.
1) Create a client callable script include to identify who is a manager. Here is an example of some simple logic for this:
var CustomReportingFilters = Class.create();
CustomReportingFilters.prototype = {
getAllManagers: function ()
{
var results = []; // create an array to hold all users who are managers
var gra = new GlideAggregate('sys_user');
gra.groupBy('manager'); // we only want one row per manager
gra.query();
// loop through the results and add to an array
while(gra.next())
{
//gs.addInfoMessage(gra.getValue(groupField)); // some debug code to see what is happenening
results.push(gra.getValue('manager'));
}
// return the array so we can use it in a condition
return results;
},
type: 'CustomReportingFilters'
};Here is what that looks like in the form:
Be sure to check the "Client callable" flag
2) Register a Dynamic Filter Option to make calling the script include simple
3) Create a report that uses the Dynamic Filter Option. You should now see a new value in is(dynamic) named "Managers".
Notice that the results of the Dynamic Filter Option are displayed in the bread crume for the filter at the top of the screen. This helps the users know what they are getting. Using Dyanmic Filter Options is operationally the same as having the user manually type in all of these names but provides a much better user experience and is dynamic as these values change.
Similar Use Case - This technique can be used to return sys_ids based on any logic you want. For instance, instead of identifying users who are listed as a manager, we could also build an array of everyone that works for me using a recursive loop. If I add this function to my CustomReportingFilters and register it as a Dynamic Filter Option, I can provide this filter as well.
Depending on the data in your instance, this may not be something you want to enable due to potential performance issues. There are other ways to solve this issue (such as materializing the hierarchy on the record as a list field) which may be better for your environment.
getMyEmployees: function (manager)
{
var emps = [];
var lastEmps = [];
var hasEmps = true;
var arrayUtil = new ArrayUtil();
// add in manager as a seed
lastEmps.push(manager);
while (hasEmps)
{
var gr = new GlideRecord('sys_user');
gr.addQuery('manager', 'IN', lastEmps); //arrayUtil.diff(lastEmps, emps));
gr.addQuery('sys_id', 'NOT IN', emps);
gr.query();
//clear our lastEmps
if (gr.hasNext() == 0) {
hasEmps = false;
}
lastEmps = [];
while (gr.next())
{
//emps.push(gr.getValue('sys_id'));
lastEmps.push(gr.getValue('sys_id'));
}
emps = arrayUtil.union(emps, lastEmps);
}
emps = arrayUtil.union([manager], emps);
return emps;
},Then you can register this as a personalized Dynamic Filter Option with this script:
new CustomReportingFilters().getMyEmployees(gs.getUserID());Use Case: Users in my assignment group with no active incidents assigned to them
Here we take the "No" Related List Condition and combine it with some additional filters to get something that helps me in my day to day work. This could be a useful report to have on a manager's dashboard to help focus available resources.
1) Create a client callable script include that gets all the users that report to that person through N levels of hierarchy.
If you are using the previous examples as well, be sure to give your script include a unique name or put this function in an existing script include (CustomReportingFilters).
2) Create a report where:
a) Call the script include to get the list of users we need to look at.
javascript:new CustomReportingFilters().getUsersInGroups(gs.getUserID());b) Use Related List Conditions to find those with no active incidents.
Conclusion
Reporting in ServiceNow is incredibly powerful but with the addition of Related List Conditions, it is even more so. Knowing how to apply them can help you focus in on more actionable reports for you and your users.
When Related List Conditions still aren't enough, Scripted Filters and Dynamic Filter Options let you encapsulate incredibly complex logic and unleash the NOW Platform in a way that is easy to consume by your users.
12 Comments
