Exploring Advance Reporting Conditions using Script

[Gunjan Kiratkar]

Introduction :

In ServiceNow, leveraging filter conditions at the list or report level is a seamless process. However, when it comes to implementing intricate or complex conditions that surpass the standard filter capabilities, it's a different ball game. This is where the need for employing scripts, like Script Includes, arises.

Script Includes serve as a robust solution, allowing us to articulate and encapsulate sophisticated logic. By crafting our intricate conditions within a Script Include, we gain the ability to extract the necessary sys_ids. These sys_ids can subsequently be utilized at the filter level, seamlessly integrating our advanced logic into the filtering process.

This approach not only empowers us to overcome the limitations of standard filtering options but also provides a structured and scalable method to manage and deploy complex conditions within ServiceNow's reporting framework.

Problem Statement : 

The objective is to generate a report from the incident table that delineates incidents within specific periods. Period 1 spans from January 1st to June 30th, while Period 2 covers July 1st to December 31st. The task involves dynamically determining the current period based on the present date and retrieving incidents falling within that timeframe.

For instance, if today's date were December 10th, 2023, the system would identify this as the 2nd period. Consequently, it should retrieve and display all incidents created between July 1st and December 31st of the current year. This dynamic approach ensures the report reflects incidents relevant to the ongoing period, simplifying the process of monitoring and analyzing incident data within the specified time ranges.

Implementation : 

In this approach we are taking help from script include to craft our problem statement logic in that script.

1. Navigate to Script Include under System Definition.

Create Script Include as shown below :

Fig. Script Include Code

Code :

var getCurrentCycle = Class.create();
getCurrentCycle.prototype = Object.extendsObject(AbstractAjaxProcessor, {

	getCurrentCycleIncident: function(){
		var incArr=[];
		var currentDate= new GlideDate();
		var startDate;
		var endDate;
		if(currentDate.getMonth()>=1 && currentDate.getMonth()<=6){ //Period 1
		startDate=currentDate.getYear()+'-01-01';
		endDate=currentDate.getYear()+'-06-30';
		}else {
			startDate = currentDate.getYear() + '-07-01';
			endDate = currentDate.getYear() + '-12-31';
		}
		var grINC=new GlideRecord('incident');
		grINC.addQuery('sys_created_on','>=',startDate);
		grINC.addQuery('sys_created_on','<=',endDate);
		grINC.query();
		while(grINC.next()){
			incArr.push(grINC.sys_id.toString());
		}
		return incArr;

	},

    type: 'getCurrentCycle'
});

2. Navigate to the report Reports -> Create New 

3. Select table as incident

Fig. Report on Incident

3. Apply filter as below in the report :

Fig. Report Filter

Video Implementation & Testing :

Value added in your knowledge ??? Then please mark it as helpful, bookmark it for future use and also share it with your ServiceNow squad.

Regards,
Gunjan Kiratkar
Community MVP 2023
Community Rising Star 2022
Youtube : ServiceNow Guy

[Wade Clairmont]

Quick question, would there be anything that would cause scoped tables and SIs to not return the array?

I created the SI in Global scope on a scoped table, verified the array was populated outside in background script.  Created the report with the advanced condition, but there are no records displayed in the report.

Thoughts?

[Jorn van Beek]

First thing I would say is cross-scope privileges

[Ankit Balapure]

Hi @Gunjan Kiratkar, it is not working for me, as I am doing exactly what you have done here, but it is not taking the sys_ids from the script include in the condition builder.

However, when I tried to call the script include in the background script it is giving me all the desired sys_ids.

I called the script include like this in condition builder:

Script include which I have defined:

Please suggest something, where am I doing the mistake?

Thanks, 

Ankit

[Jorn van Beek]

if you run from a background script you run the code server side. but if you run it from a query it is running from client side. You got client callable on, but do you also have the execute rights for the script include? (having a execute ACL.)

My guess is the script is not executed.

You should see the script running when placing a breakpoint and starting debug, or you can ofcourse add a log statement.

[Ankit Balapure]

Hi @Jorn van Beek,

Thanks for the reply, the issue got sorted already.

In the version above Washington DC, the script include is having a checkbox called 'Sandbox enabled' when you will check that, you will be able to get the data, when you will call your script include in the condition builder irrespective of the 'Client callable' field on the script include.

But if your instance version is not above Washington DC then you need to use the client callable checkbox in the script include and call that in the condition builder.