Time Spent Report

[ljbrown]

We'd like to capture time spent on each call ticket and report on this. I have achieved this by adding an additional activity to the ticket which is mandatory to complete the time spent field. I haven't used the timesheet manager module as I am led to believe this can't be mandated on the resolution of calls.

The trouble I am now having is producing the relevant report to include time spent alongside request fields such as call id, owner, SLA measures etc. Could someone please advise which tables to join together to capture this data together?

Many thanks

Lucy

[ljbrown]

Is anyone able to help with this query?

[SamS]

Hi @ljbrown,

The eternal problem with "time spent" is what one means by that.

1) IF what is meant is the time between logging the request and the time it was resolved, then showing those fields and calculating the difference will give one the answer. This would also hold that if a request is logged on Friday 16:45 and resolved on Monday 09:15, the time spent will include the weekend.

2) IF what is meant is SLA time (i.e. the clock stops at Friday 17:00 and starts at 09:00 on Monday), then the h_fixtime field keeps track of the SLA seconds between logging and resolving (1800sec = half-an-hour). Naturally h_fixtime only has a relevant value if the call has actually been resolved with an SLA.

3) IF what is meant is how long someone has actually worked on the request, then the Timesheet Manager would keep track of that.

There is probably another way of interpreting "time spent", so please let me know if I haven't covered your specific requirement.

 

[ljbrown]

Thank you for responding @SamS, what I'm trying to report on is the field when completing an activity within a ticket, as often we don't work on a ticket for the entire time it is open. We don't wish to start using the timesheet manager as this is excessive for our needs and won't mandate the field from needing to be completed.

[SamS]

Hi @ljbrown,

Task information is stored in the h_sys_tasks table. You are likely interested in teh h_created_on, h_completed_on and h_obj_ref_urn fields.