Need help on interactive filter report conditions

[Radhika11]

Hello,

I have been spending many hours trying to get the interactive report filter the correct data. As of now, I'm still could not get it right.  I only want to displays "Request Item" and "Change Request" from the dropdown list. Thank You

Here is the screenshot of the Interactive Filter:  

However, I keep getting all records returned:

[AnveshKumar M · Accepted solution]

Hi @Radhika11 ,

Here you are trying to apply filter on a field of referenced record (Task Type is field in Task Base table). In this case custom filter using Content Block is the way to achieve your requirement. I simulated your requirement and found the solution. Follow along to achieve your requirement.

Dashboard:

Created a Dashboard with 3 widgets,

1. Content Block - Dynamic Filter
2. Interactive Filter - Choice List
3. The Report - Approval table

1. Content Block - Dynamic Filter

In a Dashboard, Click on Add widget, then select Widget Category as "Content Blocks" and Select "*New Dynamic Content" then click on "Add".

Once the widget is added to dashboard click on "Click Here to configure this reusable Dynamic Content Block" as shown below.

In the new Dynamic Content record form fill in the name as "Approval Task Type" and the "Dynamic Content" Script field with the following script and save the form.

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	<g:evaluate var="jvar_tasktypes" object="true" jelly="true">
		var tables = 'change_request,sc_req_item';
		var obj=[];
		var gr= new GlideRecord('sys_db_object');
		gr.addEncodedQuery('super_classISNOTEMPTY^ORname=task^nameIN' + tables);
		gr.addOrderBy('name');
		gr.query();
		while(gr.next()){
			obj.push([gr.getValue('name'),gr.getValue('label')]);
		}
		obj;
	</g:evaluate>
	
	<select role="listbox" aria-required="false" aria-labelledby="filter_approval_task_type" name="filter_approval_task_type" id="filter_approval_task_type" onchange="filterTaskType()" style="; " class="form-control  ">
		<option value="" selected="SELECTED" role="option">All</option>
		<j:forEach items="${jvar_tasktypes}" var="jvar_tasktype">
			<option value="${jvar_tasktype[0]}" role="option">${jvar_tasktype[1]}</option>
		</j:forEach>
	</select>

	<script>
		var my_dashboardMessageHandler = new DashboardMessageHandler("filter_approvaltasktype");
		
		function filterTaskType(){
			var taskType = document.getElementById('filter_approval_task_type').value;
			if (taskType)
				publishFilter(taskType);
			else
				clearFilter()
		}
		filterTaskType();
    
		function publishFilter (taskType) {
			var filter_message = {};
			filter_message.id = "filter_approvaltasktype";
			filter_message.table = "sysapproval_approver";
    
			filter_message.filter = 'sysapproval.sys_class_name=' + taskType;
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message.id,
                filters: [filter_message]
            }, false);
			my_dashboardMessageHandler.publishFilter(filter_message.table, filter_message.filter);
		}
    
		function clearFilter() {
			var filter_message = {};
			filter_message.id = "filter_approvaltasktype";
			filter_message.table = "sysapproval_approver";
			filter_message.filter = "";
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message.id,
                filters: [filter_message]
            }, false);
			my_dashboardMessageHandler.removeFilter();
		}
	</script>
</j:jelly>

2. Interactive Filter - Choice List:

Now create a Interactive Filter of Choice List type as the one below and add to the Dashboard.

3. Create a Report: I created a report on approval table like the one below and add to the form.

After adding the report to the dashboard, click on Edit Widget (Gear Icon) of Report, then Enable interactive filter.

That's It!!

Thanks,

Anvesh

[AnveshKumar M · Accepted solution]

Hi @Radhika11 ,

1. For Content Block - Search using Task Number: Try using the following 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">
	
	<input type="text" id="filter_approval_task_search" name="filter_approval_task_search" aria-required="true" onchange="filterTaskSearch()" maxlength="100" style="; " autocomplete="off" class="form-control " spellcheck="false" aria-label="Search Task"></input>

	<script>
		var my_dashboardMessageHandlerSearch = new DashboardMessageHandler("filter_approvaltasksearch");
		
		function filterTaskSearch(){
			var task = document.getElementById('filter_approval_task_search').value;
			if (task){
				publishFilterSearch(task);
			}
			else
				clearFilterSearch()
		}
		filterTaskType();
    
		function publishFilterSearch (task) {
			var filter_message_search = {};
			filter_message_search.id = "filter_approvaltasksearch";
			filter_message_search.table = "sysapproval_approver";
			filter_message_search.filter = 'sysapproval.number=' + task;
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message_search.id,
                filters: [filter_message_search]
            }, false);
			my_dashboardMessageHandlerSearch.publishFilter(filter_message_search.table, filter_message_search.filter);
		}
    
		function clearFilterSearch() {
			var filter_message_search = {};
			filter_message_search.id = "filter_approvaltasktype";
			filter_message_search.table = "sysapproval_approver";
			filter_message_search.filter = "";
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message_search.id,
                filters: [filter_message_search]
            }, false);
			my_dashboardMessageHandlerSearch.removeFilter();
		}
	</script>
</j:jelly>

Just enter the task number in the field and click outside, it will load the results. You can make this more interactive by adding a button like "Search", so that user enters the number and clicks on search.

2. Modify Report to shows only the requests that related to me and the approvers that needed to approve it

For this you can use dynamic filters on report filters, conditions be like,

Approval For -> Requested Item -> Requested For ::: javascript: new currentUserCustom().getUserId()

(OR)

Approval For -> Requested Item -> Requested For ::: is(Dynamic) ::: Me

Thanks,

Anvesh

[AnveshKumar M · Accepted solution]

@Radhika11 

Use below code for content block for search button.

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	
	<div class="container">
		<div class="row">
			<div class="col-sm-9">
				<input type="text" id="filter_approval_task_search" name="filter_approval_task_search" aria-required="true"  maxlength="100" style="; " autocomplete="off" class="form-control " spellcheck="false" aria-label="Search Task"></input>
			</div>
			<div class="col-sm-3">
				<button class="btn btn-default" style="white-space: nowrap" type="submit" value="sysverb_update" onclick="filterTaskSearch()" id="filter_approval_task_search_button" name="filter_approval_task_search" aria-expanded="false">Search</button>
			</div>
		</div>
	</div>
	
	<script>
		var my_dashboardMessageHandlerSearch = new DashboardMessageHandler("filter_approvaltasksearch");
		
		function filterTaskSearch(){
			var task = document.getElementById('filter_approval_task_search').value;
			if (task){
				publishFilterSearch(task);
			}
			else
				clearFilterSearch()
		}
		filterTaskType();
    
		function publishFilterSearch (task) {
			var filter_message_search = {};
			filter_message_search.id = "filter_approvaltasksearch";
			filter_message_search.table = "sysapproval_approver";
			alert('sysapproval.number=' + task);
			filter_message_search.filter = 'sysapproval.number=' + task;
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message_search.id,
                filters: [filter_message_search]
            }, false);
			my_dashboardMessageHandlerSearch.publishFilter(filter_message_search.table, filter_message_search.filter);
		}
    
		function clearFilterSearch() {
			var filter_message_search = {};
			filter_message_search.id = "filter_approvaltasktype";
			filter_message_search.table = "sysapproval_approver";
			filter_message_search.filter = "";
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message_search.id,
                filters: [filter_message_search]
            }, false);
			my_dashboardMessageHandlerSearch.removeFilter();
		}
	</script>
</j:jelly>

For Javascript in Filter,

The field should be sys_id, and operator should be "is or is not etc." if the field is reference type dot walk upto sys_id like the one below.

Thanks,

Anvesh

[AnveshKumar M · Accepted solution]

Hi @Radhika11 ,

Here you are trying to apply filter on a field of referenced record (Task Type is field in Task Base table). In this case custom filter using Content Block is the way to achieve your requirement. I simulated your requirement and found the solution. Follow along to achieve your requirement.

Dashboard:

Created a Dashboard with 3 widgets,

1. Content Block - Dynamic Filter
2. Interactive Filter - Choice List
3. The Report - Approval table

1. Content Block - Dynamic Filter

In a Dashboard, Click on Add widget, then select Widget Category as "Content Blocks" and Select "*New Dynamic Content" then click on "Add".

Once the widget is added to dashboard click on "Click Here to configure this reusable Dynamic Content Block" as shown below.

In the new Dynamic Content record form fill in the name as "Approval Task Type" and the "Dynamic Content" Script field with the following script and save the form.

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	<g:evaluate var="jvar_tasktypes" object="true" jelly="true">
		var tables = 'change_request,sc_req_item';
		var obj=[];
		var gr= new GlideRecord('sys_db_object');
		gr.addEncodedQuery('super_classISNOTEMPTY^ORname=task^nameIN' + tables);
		gr.addOrderBy('name');
		gr.query();
		while(gr.next()){
			obj.push([gr.getValue('name'),gr.getValue('label')]);
		}
		obj;
	</g:evaluate>
	
	<select role="listbox" aria-required="false" aria-labelledby="filter_approval_task_type" name="filter_approval_task_type" id="filter_approval_task_type" onchange="filterTaskType()" style="; " class="form-control  ">
		<option value="" selected="SELECTED" role="option">All</option>
		<j:forEach items="${jvar_tasktypes}" var="jvar_tasktype">
			<option value="${jvar_tasktype[0]}" role="option">${jvar_tasktype[1]}</option>
		</j:forEach>
	</select>

	<script>
		var my_dashboardMessageHandler = new DashboardMessageHandler("filter_approvaltasktype");
		
		function filterTaskType(){
			var taskType = document.getElementById('filter_approval_task_type').value;
			if (taskType)
				publishFilter(taskType);
			else
				clearFilter()
		}
		filterTaskType();
    
		function publishFilter (taskType) {
			var filter_message = {};
			filter_message.id = "filter_approvaltasktype";
			filter_message.table = "sysapproval_approver";
    
			filter_message.filter = 'sysapproval.sys_class_name=' + taskType;
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message.id,
                filters: [filter_message]
            }, false);
			my_dashboardMessageHandler.publishFilter(filter_message.table, filter_message.filter);
		}
    
		function clearFilter() {
			var filter_message = {};
			filter_message.id = "filter_approvaltasktype";
			filter_message.table = "sysapproval_approver";
			filter_message.filter = "";
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message.id,
                filters: [filter_message]
            }, false);
			my_dashboardMessageHandler.removeFilter();
		}
	</script>
</j:jelly>

2. Interactive Filter - Choice List:

Now create a Interactive Filter of Choice List type as the one below and add to the Dashboard.

3. Create a Report: I created a report on approval table like the one below and add to the form.

After adding the report to the dashboard, click on Edit Widget (Gear Icon) of Report, then Enable interactive filter.

That's It!!

Thanks,

Anvesh

[Radhika11]

Good morning @AnveshKumar M 

This a fantastic step by step instructions.  It was clear and easy to follow. It is working perfectly.

There is no way I can get this to work without your help.

Thank you so much for your time and effort you have been contributing to the community.  It really makes a difference.

The following questions are for my own learning purposes. I have tried to modify your code to work for the requirement, but failed.

1. Create a Content Block - Dynamic Filter for “Approval Number” so that end user can lookup Change Request or Requested Item by Number.
2. Modify Report to shows only the requests that related to me and the approvers that needed to approve it.

I sincerely thank you for helping!

[AnveshKumar M · Accepted solution]

Hi @Radhika11 ,

1. For Content Block - Search using Task Number: Try using the following 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">
	
	<input type="text" id="filter_approval_task_search" name="filter_approval_task_search" aria-required="true" onchange="filterTaskSearch()" maxlength="100" style="; " autocomplete="off" class="form-control " spellcheck="false" aria-label="Search Task"></input>

	<script>
		var my_dashboardMessageHandlerSearch = new DashboardMessageHandler("filter_approvaltasksearch");
		
		function filterTaskSearch(){
			var task = document.getElementById('filter_approval_task_search').value;
			if (task){
				publishFilterSearch(task);
			}
			else
				clearFilterSearch()
		}
		filterTaskType();
    
		function publishFilterSearch (task) {
			var filter_message_search = {};
			filter_message_search.id = "filter_approvaltasksearch";
			filter_message_search.table = "sysapproval_approver";
			filter_message_search.filter = 'sysapproval.number=' + task;
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message_search.id,
                filters: [filter_message_search]
            }, false);
			my_dashboardMessageHandlerSearch.publishFilter(filter_message_search.table, filter_message_search.filter);
		}
    
		function clearFilterSearch() {
			var filter_message_search = {};
			filter_message_search.id = "filter_approvaltasktype";
			filter_message_search.table = "sysapproval_approver";
			filter_message_search.filter = "";
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message_search.id,
                filters: [filter_message_search]
            }, false);
			my_dashboardMessageHandlerSearch.removeFilter();
		}
	</script>
</j:jelly>

Just enter the task number in the field and click outside, it will load the results. You can make this more interactive by adding a button like "Search", so that user enters the number and clicks on search.

2. Modify Report to shows only the requests that related to me and the approvers that needed to approve it

For this you can use dynamic filters on report filters, conditions be like,

Approval For -> Requested Item -> Requested For ::: javascript: new currentUserCustom().getUserId()

(OR)

Approval For -> Requested Item -> Requested For ::: is(Dynamic) ::: Me

Thanks,

Anvesh

[Radhika11]

Good morning @AnveshKumar M 

Thank you so much for helping.  I was very impressed with your programing skills and experience on ServiceNow.

Adding a “Search” button is a great ideal.  However, it will take me weeks or months to figure it out how to do it or could not figure it out at all. 

1. Could I create post a new question and assign it to you for helping with the search button?
2. The conditions for Dynamic work fine. However, I could not figure it out which operator to use for javascript?  I would like to learn how to do both methods.

[AnveshKumar M · Accepted solution]

@Radhika11 

Use below code for content block for search button.

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	
	<div class="container">
		<div class="row">
			<div class="col-sm-9">
				<input type="text" id="filter_approval_task_search" name="filter_approval_task_search" aria-required="true"  maxlength="100" style="; " autocomplete="off" class="form-control " spellcheck="false" aria-label="Search Task"></input>
			</div>
			<div class="col-sm-3">
				<button class="btn btn-default" style="white-space: nowrap" type="submit" value="sysverb_update" onclick="filterTaskSearch()" id="filter_approval_task_search_button" name="filter_approval_task_search" aria-expanded="false">Search</button>
			</div>
		</div>
	</div>
	
	<script>
		var my_dashboardMessageHandlerSearch = new DashboardMessageHandler("filter_approvaltasksearch");
		
		function filterTaskSearch(){
			var task = document.getElementById('filter_approval_task_search').value;
			if (task){
				publishFilterSearch(task);
			}
			else
				clearFilterSearch()
		}
		filterTaskType();
    
		function publishFilterSearch (task) {
			var filter_message_search = {};
			filter_message_search.id = "filter_approvaltasksearch";
			filter_message_search.table = "sysapproval_approver";
			alert('sysapproval.number=' + task);
			filter_message_search.filter = 'sysapproval.number=' + task;
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message_search.id,
                filters: [filter_message_search]
            }, false);
			my_dashboardMessageHandlerSearch.publishFilter(filter_message_search.table, filter_message_search.filter);
		}
    
		function clearFilterSearch() {
			var filter_message_search = {};
			filter_message_search.id = "filter_approvaltasktype";
			filter_message_search.table = "sysapproval_approver";
			filter_message_search.filter = "";
			SNC.canvas.interactiveFilters.setDefaultValue({
                id: filter_message_search.id,
                filters: [filter_message_search]
            }, false);
			my_dashboardMessageHandlerSearch.removeFilter();
		}
	</script>
</j:jelly>

For Javascript in Filter,

The field should be sys_id, and operator should be "is or is not etc." if the field is reference type dot walk upto sys_id like the one below.

Thanks,

Anvesh