Jump to content

Reporting on Human Task Completion


SJEaton
 Share

Recommended Posts

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   

Link to comment
Share on other sites

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.

Screen Shot 2017-06-02 at 14.14.46.png

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

Link to comment
Share on other sites

Guest Ehsan

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

Filter:

Screen Shot 2017-06-02 at 14.29.47.png

Preview:

Screen Shot 2017-06-02 at 14.29.33.png

 

Link to comment
Share on other sites

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

Screen Shot 2017-06-02 at 16.22.57.png

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

 

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Screen Shot 2017-06-05 at 21.22.34.png

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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

 

 

Link to comment
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
 Share

×
×
  • Create New...