TomW

I found this elsewhere on the forum which dates back to 2017 but has been updated in July this year (2019): Discussion on Timesheet Manager

Thanks for your reply. I can see the timesheet for myself but I need to be able to pull up a report on how much time has been spent working on calls per team or project etc.

Is there a way to find how much time has been spent working on a call? I know that you can find out how much time a call has been open for "current date - date raised" or "date resolved - date raised", but I am looking for the time the call has been worked on e.g. when someone adds the time that they have spent working on the call when making an update or resolving it. Please could you point me in the write direction i.e. what tables and fields that I should be look at? Thanks Tom

I just had a look and yes this does only appear in the preview window. If you run the report the dates are the normal. It looks like the preview window fix was missed in the last batch of updates.

When looking for re-scheduled change requests I noticed a discrepancy in the time zone types. The dates stored in the 'Start Time' and 'End Time' fields are in GMT (Greenwich Mean Time), but in the scheduled date shown in the Timeline of the call it appears in BST (British Summer Time) which is one hour ahead! Is this a system setup issue, a minor issue with Hornbill, or something else altogether?

Thanks for the information about auto-closing tasks, I'll speak to the relevant people in our office about this. The reports also look useful as it should reduce the amount of work needed for finding old calls and overdue activities (the list of these reports is growing and some are quite complex).

I have been asked to chase up tasks that are older than a certain date. I can find the tasks older than this date, but have noticed that there are a lot of 'approval' tasks which are still active, but belong to closed calls. These tasks are the ones that were sent out to all people who are in a group of approvers, where only one or some of them need to give approval for the call to progress. Is there a way 'auto-close' these tasks once a call has been closed or cancelled? The only way I can think of stopping this from happening in the first place, is in the business process that processes the call. Although this won't resolve this in calls that were raised before the business process change. Hmm

Thanks for your recommendation Armando, when I tried it I ended up getting a MariaDB error message about incorrect syntax appearing where the counter would be showing. I have adapted what you recommended and have managed to get it working. To do this I selected 'AVG' from the drop down list under 'SELECT' (COUNT wouldn't work as it counts rows from a list where as AVG works on numbers). I put 'h_itsm_requests,h_sm_relationship_entities' in the text box under 'FROM' In the @WHERE' box I typed 'h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id' (I'm not sure this actually does anything) In the little text box between the 'SELECT' and 'DATA FORMAT' drop downs I pasted in: Finally after much palava it works. I have adapted the above for releases too by changing the 'CH%' wording, after the 'LIKE' statements, to 'RM%'. My brain is now numb!

I am trying to calculate a metric to show the average percentage of incidents associated with changes by dividing 'the number of incidents linked to changes' by 'the total number of changes' and multiplying the result by 100. I would like to show this in a widget and using a count list I am able to calculate the following. Number of incidents linked to changes: "SELECT COUNT h_sm_relationship_entities.h_entity_id FROM h_itsm_requests,h_sm_relationship_entities WHERE (h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id) AND (h_sm_relationship_entities.h_linked_entity_id LIKE 'IN%') AND (h_sm_relationship_entities.h_entity_id LIKE 'CH%')" Total number of changes: "SELECT COUNT h_itsm_requests.h_pk_reference FROM h_itsm_requests WHERE h_itsm_requests.h_pk_reference LIKE 'CH%'" But the metric for this is difficult. I currently have the following but the system doesn't like it: "( (SELECT COUNT h_sm_relationship_entities.h_entity_id FROM h_itsm_requests,h_sm_relationship_entities WHERE (h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id) AND (h_sm_relationship_entities.h_linked_entity_id LIKE 'IN%') AND (h_sm_relationship_entities.h_entity_id LIKE 'CH%')) DIV (SELECT COUNT h_itsm_requests.h_pk_reference FROM h_itsm_requests WHERE h_itsm_requests.h_pk_reference LIKE 'CH%') ) * 100" If anyone could offer some advice, or point me in the right direction if I am barking up the wrong tree, then please let me know. Cheers Tom

I am trying to find open tasks that have been assigned to a specific person i.e. approval tasks, so that I can chase them up with the assignee. I was planning on doing this by creating a report that shows all their unanswered tasks. What tables do I need to search in order to do this? Thanks

This worked thanks.

Is there a way to access comments that appear in the timeline for a request? I ask because I am trying to create a report that will show the last entry that was made in the timeline for a request. I have had a good look but I can't find a table that would contain these comments. Thanks

That's great thanks!

I am trying create a report to show incidents that are linked to a change request. I know that you can link incidents and requests using 'Linked Services' within an incident, but I can't figure out how to show these links when generating a report. I have tried banging my head on the desk but this doesn't work either! Please could you offer me some advice? Thanks

Thanks for your reply Steven. I am looking at ways to show KPIs (Key Performance Indicators) which are calculated from Metrics. Some of the figures that I use to get the metrics are gleamed from SupportWorks and some from elsewhere e.g. changes to virtual machines. Some of this information can't be gathered from SupportWorks as they are from outside the system. At the moment I am using Excel to store some of the metrics and then calculate the KPIs from the metrics and show the information on a chart. PowerBi is something that I am considering using, but since some of the KPIs are obtained through Service Manager in SupportWorks and shown in Widgets on a dashboard, it would be nice to just copy a chart from Excel and show it in a widget on the same dashboard so everything is in one place without having to invest extra time in using another system like PowerBi. I figured I should just ask before diving down the PowerBi route. In reply to your question, the audience in this instance our team and department (IT) as we can use the KPIs to see what needs to be improved as part of CI (Continuous Improvement), very ITIL!