Hi @shantanudes ,
To create a report in ServiceNow that fetches open Requested Items (RITMs) with all their associated RITM Tasks closed, you’ll need to use a combination of data modeling and filtering logic, because this type of query involves evaluating child records (tasks) in relation to their parent (RITM). Here's a clear step-by-step approach explained in paragraphs
Create a Database View (Optional but Helpful)
To make reporting across multiple tables easier, consider creating a database view. Go to System Definition > Database Views, create a new view, and include the sc_req_item table and sc_task table. Join them using sc_req_item.sys_id = sc_task.request_item. This will allow you to report on RITMs alongside their tasks in a single dataset.
Add a Custom Field or Script Include to Flag Valid RITMs
Since you want to find RITMs where all tasks are closed, you can write a Script Include or Business Rule that evaluates this condition. For example, the script can check if there are any open tasks (state != 3) for a given RITM. If not, you mark a field on the RITM, such as u_all_tasks_closed, as true. This logic ensures each RITM is dynamically evaluated as task statuses change
Create a Report Using the Custom Logic
Once the custom field is populated (e.g., u_all_tasks_closed = true), go to Reports > Create New and choose Table as sc_req_item. Use a filter like:
This report will now only include open RITMs for which all associated tasks have been completed.
You’ll get a report that shows only those RITMs that are still open, where all their tasks are already closed, helping you identify pending approvals, workflow errors, or automation opportunities.
