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.

[Saikiran Gudur1]

Hi Koyelguha,

Below is the script for your requirement:

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){
gs.print(gr.number+" tasks counts :: totaltaskscount: "+totCnt+" - closedtaskscount: "+closCnt);
gs.print("Issue Item : "+gr.number);
}
}

Please mark this as "Correct Answer" if I have given you a sufficient answer to your question.

If you are viewing this from the Community inbox you will not see the correct answer button.   If so, please review How to Mark Answers Correct From Inbox View.

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

[Koyel Guha]

Hello Saikiran,

Thank You for sharing the script.

I am actually not aware where to add this as i am new to this scripting part of service now.

Could you please guide me as where to write this script so that i can build the report on this.

I am not sure on how to begin with this and how i can create a report on this after applying this script.

Thank You in advance.

[Saikiran Gudur1]

Hi Koyel, This is a background script you can run to fetch the details. Navigate to "scripts background" in your instance left navigation panel and enter this script and run it. This custom requirement not directly possible with reporting. If you are viewing this from the Community inbox you will not see the correct answer button. If so, please review How to Mark Answers Correct From Inbox View. Thanks, Saikiran Guduri (NOW) (Please mark the answer as correct answer/helpful if it helps)

[Tracy Davis]

I don't think you should need a script for this.

I have a similar report using the TASK table. This is looking for TASKS that are closed or cancelled. Task type = Requested Item

Parent refers to the RITM.

Although, I'd recommend using the Parent.ACTIVE field instead of the state field of the RITM.

This will pull back Active RITM's with closed/completed tasks.

Fore report, include columns:

Task type, Number, State, Parent, Parent.State