Add scripting to conditions of report

[Shane18]

Hi,

I'm trying to add scripting to a report that I am creating, but I'm having some trouble and can't figure out how to do this. What I'm trying to achieve is the following: I want to show incidents that have not been updated within the last 24 hours. However, I do not want to include the weekends in this 24-hour count. I could do this by scripting - I just don't know how to add scripting to the conditions of a report.

Any help appreciated, thanks!

[davida1]

This is great, and what I was looking for (I thought), but when trying to use this method on the 'closed_at' field, the operator "is one of" is not available, only static time choices.

Any suggestion on how I can do something like:

javascript:'closed_at>=javascript:gs.monthsAgoStart(3)^closed_at>=javascript:gs.monthsAgoEnd(3)^EQ^ORDERBYsys_created_on';

The goal is three reports; closed the previous month, two months back, and three months back. The first is simple, the other two, not so much.

Thanks for any help!

David A

[Shaun19]

Its actually quite simple to add Javascript to a report. If you include the  following in the filter conditions:

javascript:new reporting_utils().fcr_exception()

This will call the code called 'reporting_utils' from the Script Includes library. Inside this code is a section called 'fcr_exception'. 

The code that will be called needs the following syntax:

var reporting_utils = Class.create();
reporting_utils.prototype = {
initialize: function() {
},
//All of these conditions must be met

//Number isoneof javascript:new reporting_utils().fcr_exception()
fcr_exception: function() {
**your scripting goes here**

type: 'reporting_utils'
};

This example uses a script to create a list of incident numbers that OOB reporting isn't capable of creating. This list is passed to the report as an array, which is then read one at a time. 
It would be a simple matter to write a script to calculate which incidents have been updated in the last 24hrs, subtract weekends, load these incidents into an array, then send the array to the report criteria.

[Shane18]

Hey Shaun,

I tried using your solution, but I must be doing something wrong? 

Script Include:

var IncidentReporting = Class.create();
IncidentReporting.prototype = {
	initialize: function() {
	},

	//Get incidents that have not been updated in 24 hours. Do not include weekends.
	getIncidentsNotUpdated: function() {
		gs.log('Script is being executed.');
		var recordsSysIdArr = [];
		var today = new GlideDateTime();

		var daysToSubtract = new GlideDateTime();

		/*
			If today is Sunday (7), we only want to retrieve records that haven't been updated since Thursday, comparing to the current time.
			If today is Saturday (6), we only want to retrieve records that haven't been updated since Saturday, comparing to the current time.
			If today is Monday (1), we only want to retrieve records that haven't been updated since Monday, comparing to the current time.
			Else, show records that haven't been updated since yesterday, comparing to the current time.
		*/

		if(today == 1 || today == 7) {
			daysToSubtract.addDaysLocalTime(-3);
		} else if(today == 6) {
			daysToSubtract.addDaysLocalTime(-2);
		} else {
			daysToSubtract.addDaysLocalTime(-1);
		}

		var gr_getNotUpdatedRecords = new GlideRecord('incident');
		gr_getNotUpdatedRecords.addQuery('sys_updated_on', '<=', daysToSubtract);
		gr_getNotUpdatedRecords.query();

		while(gr_getNotUpdatedRecords.next()) {
			recordsSysIdArr.push(gr_getNotUpdatedRecords.sys_id.toString());
		}

		return recordsSysIdArr;
	}, 

	type: 'IncidentReporting'
};

Filter condition (using classic UI - I did not use a report source):

Sys ID - is one of - javascript:new IncidentReporting.getIncidentsNotUpdated()

[dvp]

Can you update the condition in report as follows

Sys ID - is one of - javascript:new IncidentReporting().getIncidentsNotUpdated()

[Shane18]

Good catch, thank you. I found that making the script client callable and doing what you suggested fixed the issue. It's now working, thanks, everyone!

For those wondering - This is the client callable script include I used:

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

	//Get incidents that have not been updated in 24 hours. Do not include weekends.
	getIncidentsNotUpdated: function() {
		var recordsSysIdArr = [];
		var today = new GlideDateTime();
		today.getDayOfWeekLocalTime();

		var daysToSubtract = new GlideDateTime();

		/*
			If today is Sunday (7), we only want to retrieve records that haven't been updated since Thursday, comparing to the current time.
			If today is Saturday (6), we only want to retrieve records that haven't been updated since Saturday, comparing to the current time.
			If today is Monday (1), we only want to retrieve records that haven't been updated since Monday, comparing to the current time.
			Else, show records that haven't been updated since yesterday, comparing to the current time.
		*/
		
		if(today == 1 || today == 7) {
			daysToSubtract.addDaysLocalTime(-3);
		} else if(today == 6) {
			daysToSubtract.addDaysLocalTime(-2);
		} else {
			daysToSubtract.addDaysLocalTime(-1);
		}

		var gr_getNotUpdatedRecords = new GlideRecord('incident');
		gr_getNotUpdatedRecords.addQuery('sys_updated_on', '<=', daysToSubtract);
		gr_getNotUpdatedRecords.query();
		
		while(gr_getNotUpdatedRecords.next()) {
			recordsSysIdArr.push(gr_getNotUpdatedRecords.sys_id.toString());
		}
		
		return recordsSysIdArr;
	},

	type: 'IncidentReporting'
});

This is the filter condition I used (Using the old UI): Sys ID - is one of - javascript:new IncidentReporting().getIncidentsNotUpdated()