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]

Hi @Radhika11 ,

You can use exclusion list in the interactive filter (just above the Default Value in Choice List Tab in your Interactive Filter Record), add all the the records which are not needed to exclusion list. Yup, It's bit overhead work, but this is the way.

Thanks,

Anvesh

[Radhika11]

Hi @AnveshKumar M 

Here are a lot records that are unrelated.  Do I need to select all of them and moved it over to selected choices?

[AnveshKumar M]

Hi @Radhika11 ,

Interactive filter using choice list on Approval table for Task Type field will not help you, in my view. Because, If approval table contains 1000 records, you'll get 1000 choices even through you exclude them for now, they'll keep coming in as new approvals will be added to the table.

Let me know your exact requirement, I can try helping you if possible.

Thanks,

Anvesh

[Radhika11]

Good morning @AnveshKumar M 

Thank you so much for helping.

Management would like to have an interactive filter report on the Approval table where approvers or end users can see on the report who approved/rejected each request.

The report should have 2 dropdown lists (Type of Request and Approval State) so that users can select values from the dropdown lists.

Here is the screenshot of the report: