Report on all Open RITMS but all the related sctasks are closed

Sravani2 · ‎01-05-2021

Hello everyone,

I'm trying to get a list of RITMs where all the related sctasks are closed but RITM is still in open state.

I'm not able to get this through report. Can you please suggest a way to get the output.

Thanks,Sravani

Ankur Bawiskar · ‎01-06-2021

Hi,

So you will have to go with script approach

Script Include: It should be client callable

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

	getRITM:function(){
		var arr = [];
		var gr = new GlideRecord("sc_req_item");
		gr.addEncodedQuery("state=1"); // state is open
		gr.query();
		while(gr.next()) {
			var closedCount = 0;
			var taskRec = new GlideRecord("sc_task");
			taskRec.addQuery("request_item", gr.sys_id);
			taskRec.query();
			var totalCount = taskRec.getRowCount();
			while(taskRec.next()){
				if(taskRec.active.toString() == 'false'){
					closedCount++;
				}
			}

			if(closedCount == totalCount && totalCount != 0){
				// if count is same then all tasks are closed then push RITM sys_id in array
				arr.push(gr.getValue('sys_id'));
			}
		}
		return arr.toString();

	},

	type: 'ReportUtils'
});

Condition of Report

sys_id [IS ONE OF] javascript: new ReportUtils().getRITM();

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Raju Koyagura · ‎01-05-2021

Create a database view by combining both RITM and SC Task tables first and then create a report by selecting your database table name.

https://docs.servicenow.com/bundle/paris-platform-administration/page/use/reporting/task/t_CreateADatabaseView.html

Sravani2 · ‎01-05-2021

Hello Raju,

Thank you for the reply. I'm able to get the details of bot sctasks and RITMs in one report, the issue here is I just want the list of RITMs where all the related sctasks are closed.

For eg: When using a condition like RITM is open and sctasks are closed.

There might be cases where there are 10 sctasks for one RITM in which 6 are closed. So, it only shows 6 sctasks which are closed in report.

Ankur Bawiskar · ‎01-05-2021

@Sravani

Create Report on sc_req_item table

Give Condition as State [IS] Open

Then you can use Related List Conditions for Catalog Task and Select the state

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Sudeepta · ‎07-22-2021

It works without scripting