Reporting on Time Spent on a Request

[Martyn Houghton]

I am looking to report on the time spent on a request, similar what is displayed on the Request form. Is this dynamically calculated from the time sheet entries or is the total stored on the request record/entity itself?

Cheers

Martyn

[Daniel Dekel]

1 hour ago, Martyn Houghton said:

time spent on a request, similar what is displayed on the Request form

Can you explain the difference between the request and request form? I don't understand what area are you talking about.

Cheers,

Daniel

[Martyn Houghton]

@Daniel Dekel

What I am trying to find out is is the Total Time display on the request form is calculated dynamically or is the total held on the database in the request table/entity for reporting purposes?

Cheers

Martyn

[Daniel Dekel]

Got you now @Martyn Houghton, 

The entire panel is a plug-in from Timesheet Manager app and the Service Manager doe snot have that data.

I can show  you how that query works, but it will have to be  a JOIN probably to get that Total Time. 

Let me know.

Thanks,

Daniel.

[Martyn Houghton]

@Daniel Dekel

If you can let me know the query that would be a great help.

Cheers

Martyn

[Daniel Dekel]

@Martyn Houghton, the query:

SELECT
	SUM(val.h_minutes) as sum
FROM
	h_timesheet_values val 
WHERE 
	val.h_related_urn='urn:sys:entity:com.hornbill.servicemanager:Requests:PM00000048'

* PM00000048 is the Request ID

That should give you the amount of minutes that were spent on that specific request.

Hope that helps.

Daniel.

[Martyn Houghton]

@Daniel Dekel

That's great.

Thanks

Martyn

[Martyn Houghton]

@Daniel Dekel

Just tweaked the SQL a bit and re-posting for others in the future, where you want to report on multiple requests and group the totals.

SELECT
	h_related_urn,SUM(val.h_minutes) as sum
FROM
	h_timesheet_values val 
WHERE 
	val.h_related_urn in 
	('urn:sys:entity:com.hornbill.servicemanager:Requests:IDXIN00048750',
	'urn:sys:entity:com.hornbill.servicemanager:Requests:IDXIN00050908',
	'urn:sys:entity:com.hornbill.servicemanager:Requests:IDXIN00051329',
	'urn:sys:entity:com.hornbill.servicemanager:Requests:IDXIN00050713')
	Group by h_related_urn

Cheers

Martyn

[Martyn Houghton]

@Daniel Dekel

Just to confirm units are in minutes?

Cheers

Martyn

[Daniel Dekel]

@Martyn Houghton that would work, yes. units are in minutes.

Daniel.