Need to Build a report on Open RITM when all the associated tasks are closed

Koyel Guha · ‎06-20-2018

Hello Team,

I need to build a real time report to find out the Open RITMs when all the associated tasks are closed.

I have taken the condition as Request Item.State is one of (Open, Work In Progress,Pending,Pending User)

AND State is one of (Closed complete,Closed not required,Pending Cancellation,Cancelled)

But, this is not giving me the correct results as it shows Open RITMs with Closed Tasks in the report view and not showing the open tasks related to it.

However , When i am selecting/opening that perticular RITM,Iam getting other tasks which are in an open state associated with the same RITM.

Does this require any script? If yes, could you please help me with the script or how it can be done with the help of a script.

Can anybody suggest what could be the best solution to find out the same ?

Thank You.

Saikiran Gudur1 · ‎06-21-2018

Hi Koyel,

Am "Saikiran Guduri" not "saikant".

Here is your final script:-

var gr = new GlideRecord('sc_req_item');
gr.addQuery('state','IN','-5,1,2');//open states
gr.query();
while(gr.next()){
var task = new GlideRecord('sc_task');
task.addQuery('request_item',gr.sys_id);
task.query();
var totCnt = task.getRowCount();

var task1 = new GlideRecord('sc_task');
task1.addQuery('request_item',gr.sys_id);
task1.addQuery('state','IN','3,4,7');//closed states
task1.query();
var closCnt = task1.getRowCount();
if((totCnt == closCnt) && (totCnt > 0)){
while(task1.next()){
gs.print(gr.number+" - "+gr.state+" - "+task1.number+" - "+task1.state);
}
}
}

Thanks,
Saikiran Guduri (NOW)
(Please mark the answer as correct answer/helpful if it helps)

View solution in original post

Thomas G · ‎11-17-2022

In my case it was solved with a script include. See: How to call a script include / server script through reports? - Support and Troubleshooting (service...

A script include was created with the name 'ReportUtils' with the following script:

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

getRITM:function(){
var arr = [];
var gr = new GlideRecord("sc_req_item");
gr.addEncodedQuery("active=true"); // active = true
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'
});

A report was then created looking at the requested_item table with the following condition: