logo

NJP

← Back to News

Dashboards: Approaches for Interactive Filters on List fields

Import · Jan 22, 2022 · article

image

| 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:

image

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:

| image | | Open dashboard properties Select plus sign to add a widget Select "Interactive Filters" Select "Cascading Filter" |
| ---------------------------------------------------------------------------------------------------------------- | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| | | | |
| image | | Select "#New Cascading Filter" |
| | | | |
| image | | Click on the "Add" button Click on the link "Click here to configure this reusable Widget" |
| | | | |
| image | | Enter a name. This value will be displayed as the widget title. To make the filter work also on reports with child tables, this checkbox has to be set. Then save the record. Do not click on "Update"! |
| | | | |
| image | | On related list "Cascading Filter" click on "New" |
| | | | |
| image | | Enter a name. That value is displayed right above the selection within the widget. Select table "User" Select "Name" as display field. Optionally, a condition can be configured to limit the set of selectable users. Then save the record. Do not click on "Update"! |
| | | | |
| image | | At Related List "Target Tables" click on "New" |
| | | | |
| image | | Select "task" as "Target table". This way the filter is more generic and can be used for all tables which extend the task table. Select "Watch list" at "Field" Submit the form. |
| | | | |
| image | | After returning to the dashboard, you should find there the previously configured widget. In the upper right corner of the widget, click on the gear icon. |
| | | | |
| image | | Make sure that the checkbox "Act as interactive filer" is set. Modify the widget properties as needed. |
| | | | |
| image | | After adding a report on a task extended table of your choice to the dashboard, open the widget properties. Select "Follow interactive filter" By selecting "Show when following filter" a filter icon right beside the widget title is displayed. |

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:

image

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:

| image | | Open dashboard properties Select plus sign to add a widget Select "Content Blocks" Select "*New Dynamic Content" |
| ---------------------------------------------------------------------------------------------------------------- | | ---------------------------------------------------------------------------------------------------------------------------------------------------------- |
| | | | |
| image | | Click on the "Add" button Click on the link "Click here" |
| | | | |
| image | | Enter a name. This value will be displayed as the widget title Insert the Jelly-Code (see chapter "Complete Jell Code" right next to that table) and save. |
| | | | |
| image | | After returning to the dashboard, you should find there the previously configured widget. In the upper right corner of the widget, click on the gear icon. |
| | | | |
| image | | Make sure that the checkbox "Act as interactive filer" is set. Modify the widget properties as needed. |

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 &lt; arrItemSysId.length; numItemIndex++) {
      arrOut.push('&lt;li class="select2-search-choice"&gt;');
      arrOut.push('&lt;div&gt;');
      arrOut.push(arrItemName[numItemIndex]);
      arrOut.push('&lt;/div&gt;');
      arrOut.push('&lt;a href="#" onClick="removeCustomWatchListFilterItem(\'')
      arrOut.push(arrItemSysId[numItemIndex])
      arrOut.push('\');return(false)" role="button" class="select2-search-choice-close"&gt;&lt;/a&gt;');
      arrOut.push('&lt;/li&gt;');
    }

    $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

| var arrItemSysId = []; var arrItemName = []; | | These two arrays store the Sys IDs and the labels of the selected users. |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| | | | |
| var dashboardMessageHandler = new DashboardMessageHandler( "custom_watch_list_filter", function() { resetCustomWatchListFilter() }); | | 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. |
| | | | |
| $j(document).ready(function() { $j("#myCustomInteractiveFilterChoice").select2(); }); | | 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. |
| function rebuildCustomWatchListFilterUsers() { ... } | | That function builds the list of selected users right below the selection box |
| | | | |
| function publishCustomWatchListFilter() { var objFilter = { id : 'custom_watch_list_filter', table : 'task', filter : arrItemSysId.length > 0 ? 'watch_listDYNAMIC' + arrItemSysId : '' }; ... } | | 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. |
| | | | |
| function removeCustomWatchListFilterItem(strSysId) { ... } function resetCustomWatchListFilter() { ... } function addCustomWatchListFilterItem(){ ... } | | These functions take care of the management regarding the selected users. |
| | | | |
| 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 | | Preloading of all records of the sys_user table. The array arrUserList is later iterated to fill the entries for the user selection. |

Resources

image

View original source

https://www.servicenow.com/community/platform-analytics-articles/dashboards-approaches-for-interactive-filters-on-list-fields/ta-p/2301044