Reporting Requests based on Tasks/Activities

[Keith]

Hi,

Wonder if someone can explain how to join the Requests table with the associated tasks so that I can report on them. i.e. Gather a list of requests which contained a specific task with a specific outcome. 

Thanks!

 

[Lyonel]

@Keith

Good question! I actually wanted to do that for a while but never got round to actually doing it... But because it's you and I have a similar need, but mainly because things have evolved and so has my knowledge in Hornbill, here is what you need:

Note: if you alias your tables, make sure you update the table name in the "concat" function

Hope this answers your need?

[Keith]

Thanks @Lyonel looks like you've come to my rescue again.  This is exactly why this forum is a great place to be. 

Haven't tried it just yet but will give it a go later today. 

 

[Keith Stevenson]

All,

That query looks a little wrong and its unlikely to ever return so will most likely time out. We will review your requirements and post back a better solution shortly. In the meantime can we ask you not to run the above as it may degrade your instances performance whilst it locks the tables running the queries. 

 

Kind Regards

Keith Stevenson

[Keith]

OK Thanks @Keith Stevenson It did return results on a small 500 sample but times out when running for full dataset as advised. Will await further feedback

[Gary.Reynolds]

Hi,

I would like to do the same thing so will be interested in your updated post.

 

 

[Keith]

On 7/18/2018 at 11:11 AM, Keith Stevenson said:

All,

That query looks a little wrong and its unlikely to ever return so will most likely time out. We will review your requirements and post back a better solution shortly. In the meantime can we ask you not to run the above as it may degrade your instances performance whilst it locks the tables running the queries. 

 

Kind Regards

Keith Stevenson

Hi @Keith Stevenson any update on you providing a suitable solution to this?

[David Hall]

Hi @Keith

I've had a look and created a different query which should be more efficient and provide the data its seems you were looking for. 

SELECT req.h_summary, req.h_requesttype, task.h_title, task.h_outcome
FROM h_itsm_requests req 
JOIN
(SELECT SUBSTR(t.h_obj_ref_urn FROM 53) AS h_request_id, t.h_title, t.h_outcome 
 FROM h_sys_tasks t
 WHERE t.h_obj_ref_urn LIKE 'urn:sys:entity:com.hornbill.servicemanager:Requests:%'
 AND t.h_title LIKE 'Investigate%'
 AND t.h_outcome='Completed') task
ON req.h_pk_reference = task.h_request_id
LIMIT 10000

Perhaps you can adjust the title and outcome as you need, give this a go and gradually up the limit and see how that goes.

Kind Regards,

Dave.