How to obtain a list of Active Requests that either do not have any human tasks (activities) or had them but they are closed

[Berto2002]

I would like a list of Active Requests where the required work by analysts is not driven by Tasks. That means knowing all Requests where all tasks have been completed or no tasks ever have been added.

In my mind I believe this means I need a report which includes data from Tasks because you can't tell from the Request table if a Request has tasks or what status they are.

But I don't think this works:

1. If I link Requests to Tasks (e.g. as per below that we have in another report), then the report will naturally always exclude all Requests that have no Tasks at all
2. If I don't link Requests to Tasks, I cannot at all determine if a Request has or had a task and what its status is

I am trying to think through how I can get a complete list of Active Requests (period) and the task Title and Details only appears for those that do have tasks (with multirows if more than one task); where there are no tasks or only completed tasks, we see no task data.

So I cannot see a way to get my desired outcome: Requests where all tasks have been completed or no tasks ever have been added.

Any ideas please?

[Met]

Hi @Berto2002

You should use a LEFT JOIN to pull all rows from the h_itsm_requests table and only matching rows from the h_sys_tasks table. You could then look at where h_sys_tasks.h_obj_ref_urn is NULL. This should give you a list of requests which have never had a task associated with them.

If you also want to pull requests where all tasks have been completed, then I am not sure if this can be done in the Reporting area (don't currently have access to try it) without using database direct:

SELECT 
  req.h_pk_reference, 
  tasks.h_title, 
  tasks.h_details, 
  tasks.h_completed_on 
FROM 
  h_itsm_requests req 
  LEFT JOIN h_sys_tasks tasks ON CONCAT('urn:sys:entity:com.hornbill.servicemanager:Requests:', req.h_pk_reference) = tasks.h_obj_ref_urn 
WHERE 
  -- Make sure there are no tasks where h_completed_on is empty - i.e. no tasks that are outstanding
  NOT EXISTS (
    SELECT 
      1 
    FROM 
      h_sys_tasks tasks2 
    WHERE 
      CONCAT('urn:sys:entity:com.hornbill.servicemanager:Requests:', req.h_pk_reference) = tasks2.h_obj_ref_urn 
      AND tasks2.h_completed_on is null
  ) 
  -- Or pull all requests where there are no tasks
  OR NOT EXISTS (
    SELECT 
      1 
    FROM 
      h_sys_tasks tasks2 
    WHERE 
      CONCAT('urn:sys:entity:com.hornbill.servicemanager:Requests:', req.h_pk_reference) = tasks2.h_obj_ref_urn
  ) 
  And req.h_status not in ('status.closed', 'status.cancelled')
ORDER BY 
  req.h_pk_reference DESC

I think this should achieve what you want - it will pull all requests plus any tasks details where either the request has no tasks, or the request has all its tasks completed. 

There might be a simpler way to do this that I've completely missed.

 

[Berto2002]

On 19/10/2023 at 10:55, Met said:

You should use a LEFT JOIN to pull all rows from the h_itsm_requests table and only matching rows from the h_sys_tasks table. You could then look at where h_sys_tasks.h_obj_ref_urn is NULL. This should give you a list of requests which have never had a task associated with them.

@Met very kind of you to suggest this but my logic circuits don't compute what you've suggested...

Your first sentence seems to say "return all Requests linked to Tasks" but the second sentence (the filter) seems to say, "and filter out all the Requests linked to Tasks" (which will be 100% of rows found in the first part).

I have 4 tables but the link to the h_buz_activities and h_sys_accounts is just to link additional columns of data so I think what I have is Requests linked to Tasks with the Left Join you suggested.

Buz to Requests:

Requests to Accounts:

Requests to Tasks:

And has some basic filters including the one I think you suggested.

The report runs for quite a time but returns no rows.

If you can help further it would be much appreciated.

Rob

[Met]

@Berto2002

A left join on the requests table and tasks table will pull all rows from the Requests table, plus any matching rows from the Tasks table based on your join criteria. If there is no data in the Tasks table for a Request (because there are no activities on the request), it will still pull through the row from the Requests table, but the task fields will be Null for that row. So by then applying the filter Tasks->h_obj_ref_urn = NULL you will see all Requests that exist in the Requests table, but don't exist in the Tasks table (the request segment in the diagram below, without the intersetion with tasks). The REPLACE(h_sys_tasks, etc.) criteria is only used to pull the requests where the data exists in both tables. It is a bit counter-intuitive as you're using a criteria which actually you want the reverse of.

For your table joins, I would make sure you are only using a left join for the Requests -> Tasks tables. The rest should all be normal Joins.

Aside from that, the only other thing I can think of is double checking that you've chosen Value equals -> Against Custom Criteria when using the SQL functions. If you just choose 'Equals value' and put the function in the pop-up box then it will return no results. Not possible to tell from your screenshots if you've done this or not so thought it's worth double checking.

Aside from that, it should work from what I can see.

[Berto2002]

@Met I stripped the report back and started again and I can now get a result with this:

And it shows the obj ref urn data.

I then applied filters and got my list of requests without tasks.

Thanks for your help. I will now try to build back in the other data columns I wanted.

Rob