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

[Koyel Guha]

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.

[Koyel Guha]

Hello Saikiran,

The Previous script which you have posted was not giving the correct results but the current script is giving the correct results.

After running the recent script in the script background and got this :

I have also checked all the listed RITMs shown after running the script which is meeting my requirement. I could see the task is closed where as the RITM is open.

However,wanted to know 1 more thing :

Can we have a view like this below:

Where we can get the associated Task Number as well including the state ? OR We need to just get into the RITM to check the same?

Thank You so much for your help Saikant and @Tracy Davis 🙂

[Saikiran Gudur1 · Accepted solution]

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)

[Koyel Guha]

Thank You so very much Saikiran for all your help on this . Now I am getting this view 🙂

Apologies for writing the incorrect name.

[Thomas G]

Hello,

I am looking for something similar than Koyel:

A Requested Item (RITM) has one or more workflow generated SC Tasks. When all workflow generated tasks are closed, the RITM is closed automatically. It happens though, that an ITIL-user creates a manual task. When such a task is closed, the RITM does not close automatically, and we do not want it to close automatically either. 

I want to report on RITMs where all SC Tasks are closed but the RITM is not. 

I have come close but not close enough with this report on the sc_req_item table and a related list condition looking at the catalog task table:

The problem with it is, that it will also catch RITMs with active SC Tasks if there is one or more closed tasks on the same RITM, so if for instance I have a RITM with two active task and one closed, the report will show the RITM because of the single closed one. I do not want that. The report should only show active RITMs where all tasks are closed. Is this still not possible with reporting in ServiceNow?

Best regards
Thomas

[ecagle]

I would love to know the answer to this as well (how to do it via reports and not via a script).