Reporting on Human Task Completion

[SJEaton]

Hi

Is there a way of reporting on the dates when a Human Task is completed?  A lot of our recruitment performance indicators are based around time taken between one task and the next so I was wondering if I could design a report that pulls this out?

Sam   

[Guest Ehsan]

Hi @SJEaton,

You can report on Tasks through h_sys_tasks SQL table. The columns in the SQL table that capture details relating to completion of a Task are "h_completed_by" and "h_completed_on". In a Task record, you can identify the Request that the Task belongs to through "h_obj_ref_urn" column.

For example, the following Task relates to IN00000344 on my instance.

The SQL query would look like...

SELECT h_title, h_completed_by, h_completed_on FROM h_sys_tasks WHERE h_obj_ref_urn = 'urn:sys:entity:com.hornbill.servicemanager:Requests:IN00000344'

I hope this helps.

Ehsan

[Guest Ehsan]

Through Reports (Admin Tool > Hornbill Service Manager > Reports), I created the following example.

Filter:

Preview:

 

[SJEaton]

This is useful thank you, I will let you know if I have any more queries as I go along

Sam 

[SJEaton]

I'm creating a SQL Schema Designer report right??

I get as far as adding the table but then don't have the other filer tabs?

 

[Guest Ehsan]

@SJEaton Click on "Select Columns" tab in the view and you should then select the columns that you would like to see in your Report. 

[SJEaton]

Oh ok, it all comes up after that, thanks

 

[Guest Ehsan]

@SJEaton

There is also a wiki page that entails on the Reports feature. This may help you with building Reports for other requirements that you may have. Please refer to the following link (Expand "Building A New Report - SQL Schema Designer" section):

https://wiki.hornbill.com/index.php/Reports

 

[SJEaton]

I will try and follow this guide, I think I want to join my table with h_itsm_requests so I can also report on some request info (eg. date logged) but getting an error at the moment.

[Steven Boardman]

@SJEaton

If you are looking to join the h_itsm_requests table with the h_sys_tasks table the following may help:

When defining the join, use the Against Custom Criteria which will allow you to ConCat the h_obj_ref_urn column value, as you want to join on the last part of the value, with the request id in the h_itsm_requests table (h_pk_reference)

Example of a URN: urn:sys:entity:com.hornbill.servicemanager:Requests:IN00000344

So you can use the following in the Against Custom Criteria:

CONCAT("urn:sys:entity:com.hornbill.servicemanager:Requests:",h_itsm_requests.h_pk_reference)

On the Select Columns tab, you can also click on each selected column to give it a more user friendly display name

Hope that helps

Steve

 

 

 

[SJEaton]

Thanks Steve, I'll have a stab at this next week.

Have a good weekend.

Sam

[SJEaton]

This has been helpful and I've managed to get some reports up and running

Just another query.....would I be able to bring back 'task completed on' dates in columns for each request rather than each task appearing on a different row? 

Sam

[Steven Boardman]

@SJEaton

I am not sure if this is what you are looking for, but you can include the task completed on column from the h_sys_task table, and then if you create a report as a Grouped List of Data type, you can then group the report on the Request ID, giving you something like the following:

In my example i have chosen to re-label the column names, and exclude the request ID from each row, which you can do under Data Collection tab > Select Columns > Marking the Column as Not Visible

Hope that helps

[SJEaton]

Hi Steve, yea I have tried this before (although it doesn't seem to want to group at the moment even though I've ticked Group By against Request ID??)

It's not really what I'm after though and I think what I want is probably not possible. 

Sam

 

[SJEaton]

I'm actually struggling with the Select Ordering tab in general, I tick Group By and Sort Ascending or Descending against columns but it doesn't seem to do anything and then when I go back into the report at a later time the ticks have disappeared. Is there an issue with this?

Sam

[SJEaton]

Going back to my original query, this is an extract of a report that I've created that lists each request, the date received and the request response time.  What I would also like to add into this report is a column that shows the date a particular human task was completed. Can that be done as a column?   

Request Ref.	Catalog Item	Date Request Received	Request Response Time (Secs)	Within SLA? (1 = Yes)	BSO
SR00000629	I Want to Advertise	2017-05-03 06:37:31	59400	0	Holly Bishop
SR00000630	I Want to Advertise	2017-05-03 06:45:30	15960	1	Yvonne Maginley
SR00000631	I Want to Advertise	2017-05-03 09:26:54	5968	1	Husna Ali
SR00000633	I Want to Advertise	2017-05-03 10:52:15		0
SR00000645	I Want to Advertise	2017-05-03 14:38:16	35501	0	Shirley Baptiste
SR00000646	I Want to Advertise	2017-05-03 14:39:45		0	Yvonne Maginley
SR00000647	I Want to Advertise	2017-05-03 14:41:18	143751	0	William Baidoe-Ansah
SR00000648	I Want to Advertise	2017-05-03 14:42:51		0	Husna Ali
SR00000665	I Want to Advertise	2017-05-04 11:17:59	17818	1	Holly Bishop

[Victor]

@SJEaton yes, you can... The information you need is stored in h_sys_tasks table. You need to be aware that a request can have multiple tasks associated to it so remember this when building the JOIN and other filter criteria.

[Steven Boardman]

@SJEaton

From the h_sys_tasks table, you can include the h_completed_on column, which will give you the date and time the task was completed. 

As Victor points out, there maybe multiple tasks per request, so you could group the tasks by request as per my example earlier in this thread, or if you are interested in the completion of a specific task on all requests, then you could add a filter to the h_title column of each task - so that the report only returns tasks that match, and of course their completion date if you include the suggested column above. 

 

 

[SJEaton]

Thanks Guys, I'll get back to you if I have any further queries.

Sam