Report creation with all Open/inProgress RITMS but with completed sctasks

[Fotina Galieb]

Hello,

I got an issue with some RITMs which remain in state open/in progress even when all sctasks were already completed long time ago. I need to get the list of such RITMs. 

I found a great solution in postSolved: Report on all Open RITMS but all the related sctas... - ServiceNow Community , thank you @Ankur Bawiskar. However after migration to Xanadu, my report is empty.

I have created identiacal script include as client callable.

var RRRRRRR = Class.create();

RRRRRRR.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    getRITM:function(){

        var arr = [];

        var gr = new GlideRecord("sc_req_item");

        gr.addEncodedQuery("state=2"); // state is in progress

        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: 'RRRRRRR'

});

Then a report in the same application, with the filter as per solution,

Condition of Report

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

 When I checked client callable, i was asked for role, so i used itil role.

@Ankur Bawiskar do you have any idea what could be changed in Xanadu or what i did wrong? Before migration it worked perfectly.

Thank you so much in advance,

Best regards,
Fotina

[Uncle Rob]

Why do you even need scripting?  I've done this multiple times with conventional Report Conditions.
You start with a List report and make sure you leverage the Related Record conditions.
Here's a video I did on that concept.
https://youtu.be/gkzKzSXbwk0

[Fotina Galieb]

Hello @Uncle Rob ,

Hello @Ankur Bawiskar 

Thank you, in case of table sc_req_item, what should i choose in Related List conditions? Cause i dont see catalog task>task, I need to see RITMs where tasks are closed complete, but now it still shows me RITMs moxed with completed tasks and in progress tasks. But i dont need in ritms where tasks are in progress.

Thank you for your support!

[Uncle Rob]

sc_task's label is "Catalog Task".  Makes it easier to find.

[Fotina Galieb]

Hi @Uncle Rob Thank youso much for quick reply, yes I found it quickly,however it just still shows me Ritms with both tasks closed and in progress, even though I put Active is false. I thought maybe there is something else what I am missing. BTW great youtube channel, thank you for sharing!