Jump to content
Keith

Reporting Requests based on Tasks/Activities

Recommended Posts

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!

 

Share this post


Link to post
Share on other sites

@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:

image.thumb.png.84ea3f4eb844ce2c709ec01a9d75b23d.png

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

Hope this answers your need?

  • Like 1

Share this post


Link to post
Share on other sites

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. 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...