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 Tracy,

Thanks for the response.

I have tried the same condition as you suggested but I am getting "No Records".

Could you please help me with this so that I can prepare a report to see the RITM is still open when all the associated  tasks are closed.

Thank You in advance.

[Tracy Davis]

In our environment the RITM should close when the tasks are closed, so it may be that you really don't have any RITM's stuck in an open state.

You could try playing with the filter and changing to Parent.State is not Closed or Canceled and see if you get anything back that way?

Do you have an example of one that you would expect to see on this report? If yes, what is the state of the RITM? You'd also want to check and make sure there aren't any other records associated with the RITM that are still open (stories, etc.).

Hope that helps.

[Saikiran Gudur1]

HI Tracy,

Assume for a request item RITM00001  (which is Open) there are 3 tasks in it.

1 is open - TASK0001

1 is closed - TASK0002

1 is cancelled - TASK0003.

Now if you apply the above filter which you posted, on task table, it will check for a task whose record state is (closed OR cancelled) && (Active RITM), as a result you will see this.

TASK0002 - RITM00001

TASK0003 - RITM00001

( Note: TASK0001 which is in open state, as per problem statement from koyel the RITM0001 should not be reported).

But as per the requirement Koyel want a list of RITMs where all the tasks under it in closed states where the RITM is still in open state.

Understand that is a custom requirement. And they dont want the list of TASKs here. They want RITMs list by problem statement.

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

That is the reason I posted the script for it.

@KoyelGuha, could you please try my script and inform whether it is working for you?

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)){
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 questio

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]

Ok, that does make sense then, although like Koyelguha, I'm not familiar with how to run a script in the reporting environment.

[Saikiran Gudur1]

:D,

I got your point, we can't run a script in a reporting environment. BUT we can run a background script ( navigate to "<INSTANCE_URL>/sys.scripts.do" as a Admin user).

paste the above code, run the script and get the result printed on screen.

Copy that dump into a notepad/notepad++/sublimetext what ever it is tool you like to use. Do some formatting and paste the resulted&formatted text into Excel to use for reporting.

I hope you are aware of it.

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