|
A field of type "List" is nothing else than an extended form of a reference field, which can store more than one reference. They are used in many ways, and the "Watch list" for task inherited tables is certainly the most prominent example. But they also have some disadvantages. For example, it is not possible to group by these fields, which makes sense since for each record the list can have different numbers of elements. For reporting purposes, therefore, many users would like to have at least interactive filters on a dashboard that can be used to reduce a list based on the selected elements. OOTB ServiceNow does not offer interactive filters for list type fields. However, there are some workarounds I will introduce in this article.
|
|
Approach 1: Normalize List Values into a Custom Table
That approach introduces a custom table which holds single relations between the record and all list values. The normalization is done with the help of an additional Business Rule and the initial load is performed by a background script.
This approach has its charm, because it is generic and allows not only interactive filters, but also all kinds of reports. But on the other hand, it requires at least a custom table and some implementation efforts.
If you are interested in that approach, please find the complete solution on page Reporting on Lists – Part 3/3 on Conquer Challenging Reports by Leveraging the Now Platform which was written by Performance Analytics & Reporting guru Adam Stout.
In the following two chapters, I will describe my own solution proposals.
Approach 2: Leveraging Cascading Filters
After a while of trial and error I found out that Cascading Filters can be used to filter list type fields and the result looks like as follows:
Although no code is needed, this approach has a few downsides:
- You can select only one user. Unfortunately, I couldn't figure out whether it is possible to configure a multi-selection.
- After choosing a user, you have to click on the "Apply" button explicitly to apply the selection.
To configure such a filter, proceed as follows:
 |
|
|
 |
|
|
 |
|
|
 |
|
|
 |
|
|
 |
|
|
 |
|
|
 |
|
|
 |
After returning to the dashboard, you should find there the previously configured widget.
|
|
 |
|
|
 |
After adding a report on a task extended table of your choice to the dashboard, open the widget properties.
|
Approach 3: Create a Custom Interactive Filter
To have a better user experience, you can build a custom filter and adapt it according to your needs. The following Custom Filter works without the "Apply" button, and you can also select multiple users:
This pro-code approach requires your knowledge in Jelly, JavaScript, HTML and CSS and this article cannot provide an introduction to these technologies. However, in my Knowledge Sources To Go I have collected many links to helpful resources which will support you to get started with the respective topics.
Furthermore, you need to understand the nature of Custom Filters. In the last chapter "Resources" I have provided a link list regarding Custom Filters, and additionally I will go into more detail on individual sections of the source code.
Please note that my Custom Interactive Filter has the following restrictions:
- It can be placed only once on a dashboard.
- It only filters the watch_list field of task records.
- It can cause performance issues if your sys_user table has many thousand entries.
- If you select more than one user, the query criteria will be combined with an OR operator, but you could modify the code to have a AND behavior.
To build such a Custom Interactive Filter follow the instructions below:
 |
|
|
 |
|
|
 |
|
|
 |
After returning to the dashboard, you should find there the previously configured widget.
|
|
|
|
||
 |
|
Complete Jelly Code:
<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<script>
var arrItemSysId = [];
var arrItemName = [];
var dashboardMessageHandler = new DashboardMessageHandler(
"custom_watch_list_filter",
function() {
resetCustomWatchListFilter()
});
// transform HTML selectbox to ServiceNow selection
$j(document).ready(function() {
$j("#myCustomInteractiveFilterChoice").select2();
});
function rebuildCustomWatchListFilterUsers() {
var arrOut = [];
for (var numItemIndex = 0; numItemIndex < arrItemSysId.length; numItemIndex++) {
arrOut.push('<li class="select2-search-choice">');
arrOut.push('<div>');
arrOut.push(arrItemName[numItemIndex]);
arrOut.push('</div>');
arrOut.push('<a href="#" onClick="removeCustomWatchListFilterItem(\'')
arrOut.push(arrItemSysId[numItemIndex])
arrOut.push('\');return(false)" role="button" class="select2-search-choice-close"></a>');
arrOut.push('</li>');
}
$j("#myCustomInteractiveFilterSelectedItems ul:first").html(arrOut.join(''));
$j('#myCustomInteractiveFilter span.select2-chosen').text(arrItemSysId.length == 0 ? 'ALL' : '');
}
function publishCustomWatchListFilter() {
var objFilter = {
id : 'custom_watch_list_filter',
table : 'task',
filter : arrItemSysId.length > 0 ? 'watch_listDYNAMIC' + arrItemSysId : ''
};
SNC.canvas.interactiveFilters.setDefaultValue({
id : objFilter.id,
filters : [objFilter]
}, false);
if (arrItemSysId.length > 0) {
dashboardMessageHandler.publishFilter(objFilter.table, objFilter.filter);
}
else {
dashboardMessageHandler.removeFilter();
}
}
function removeCustomWatchListFilterItem(strSysId) {
if (typeof strSysId == 'string') {
var numArrayIndex = arrItemSysId.indexOf(strSysId);
if (numArrayIndex != -1) {
arrItemSysId.splice(numArrayIndex, 1);
arrItemName.splice(numArrayIndex, 1);
rebuildCustomWatchListFilterUsers();
publishCustomWatchListFilter();
}
}
}
function resetCustomWatchListFilter() {
arrItemSysId = [];
arrItemName = [];
rebuildCustomWatchListFilterUsers();
publishCustomWatchListFilter();
}
function addCustomWatchListFilterItem(){
var strSelectedValue = $j('#myCustomInteractiveFilterChoice option:selected').val();
var strSelectedLabel = $j('#myCustomInteractiveFilterChoice option:selected').text();
if (strSelectedValue == "all") {
resetCustomWatchListFilter();
}
else {
if (!arrItemSysId.includes(strSelectedValue)) {
arrItemSysId.push(strSelectedValue);
arrItemName.push(strSelectedLabel);
}
rebuildCustomWatchListFilterUsers();
publishCustomWatchListFilter();
}
}
</script>
<g:evaluate>
var arrUserList = [];
var grUser = new GlideRecord('sys_user');
grUser.orderBy('name')
grUser.query();
while(grUser.next()) {
arrUserList.push({
name: grUser.getValue('name'),
sysId: grUser.getUniqueValue()
});
}
</g:evaluate>
<div class="widget-content" style="padding:10px" id='myCustomInteractiveFilter'>
<select style="width:100%;" id="myCustomInteractiveFilterChoice" class="select2-search" onchange="addCustomWatchListFilterItem();">
<div class="form-horizontal container-fluid">
<option value="all">ALL</option>
<j:forEach var="jvar_type" items="${arrUserList}">
<g:evaluate var="jvar_type" jelly="true">
var strName = jelly.jvar_type.name;
var strSysId = jelly.jvar_type.sysId;
</g:evaluate>
<option value="${strSysId}" label="${strName}">${strName}</option>
</j:forEach>
</div>
</select>
<div id="myCustomInteractiveFilterSelectedItems" class="select2-container select2-container-multi interactive-filter__widget-content form_control" style="width: 100%;">
<ul class="select2-choices">
</ul>
</div>
</div>
</j:jelly>
Some aspects explained in more detail
|
These two arrays store the Sys IDs and the labels of the selected users. | |
|
|
||
|
Instantiation of the message handler for broadcasting the filters to all widgets on a dashboard. The first parameter is a unique ID for the widget the second parameter represents a call-back function which is invoked in case of resetting all widgets. |
|
|
|
||
|
After loading the HTML page the registered JQuery method select2() is invoked to transform a simple HTML select to a complex value selector inclusive look-ahead behavior. | |
|
That function builds the list of selected users right below the selection box | |
|
|
||
|
Based on the selected users, that function broadcasts a filter object (objFilter) to all dashboards widgets. The most important part is the property "filter" where the encoded query portion is built which should be added to widgets which are following interactive filters and displaying records from task inherited tables. Implemented is a query which combines all selected users with a OR condition and in case you want a AND behavior you have to modify the value at the "filter" property. |
|
|
|
||
|
These functions take care of the management regarding the selected users. |
|
|
Preloading of all records of the sys_user table. The array arrUserList is later iterated to fill the entries for the user selection. |
Resources
@Maik Skoddow I've been attempting to use approach 3 in San Diego release. When I try to edit the "Watcher (via Custom Filter)" widget to check "Act as interactive filter", the option is not listed. I've also attempted this in a nearly clean San Diego PDI and the option still does not show up for a Dynamic Content Block.
Does Approach 3 still work in San Diego release?
Is there some option or module I must activate to enable this feature?
Can I manually enable "Act as interactive Filter" by manipulating the Portal Preferences for this Widget?
Thank you!
@Greg Hoschar @Maik Skoddow
I'm having the exact same issue. Add option 3 to the Dashboard as a Content Block and there's no "Act as Interactive Filter" on the settings:
we have "VOC Survey Dashboard" as below
all reports from "Metric Result" table.
requirement is :
'Data Version' field from Case table have 3 choice, cases got created through the record producers to the case table and Data version field choice will be updated in the case table from the record producers submission,
now we want create the 'Data Version' filter on the dash board we need to map some how with the case table 'Data Version' based the filter selection report will get fileted and show the data according to case table 'Data Version' choices. Thanks
