[Active Tasks for Closed Calls]

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

[Active Tasks for Closed Calls]

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

[Problems calculating percentages using sub-queries]

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:

Quote

 

((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%')) / (SELECT COUNT(h_itsm_requests.h_pk_reference) FROM h_itsm_requests WHERE h_itsm_requests.h_pk_reference LIKE 'CH%') ) * 100

 

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!

[Problems calculating percentages using sub-queries]

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

[Searching for tasks assigned to specific user]

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

[Searching items that appear in the Timeline]

This worked thanks.

[Searching items that appear in the Timeline]

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

[Linked incidents and change requests]

That's great thanks!

[Linked incidents and change requests]

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

[Show Excel charts in Widget]

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!

[Show Excel charts in Widget]

Is it possible to show an Excel chart in a Widget, i.e. by linking to the spreadsheet or even just showing an image of the chart?

 SomeSomething like this may have appeared in the forum before but I am unable to find anything related to this.

Thanks in advance

Tom

[Is there a way to check if a change request has been re-scheduled?]

On a similar note to the question I asked above, is there a way to check how many times a call has been re-assigned?

[Is there a way to check if a change request has been re-scheduled?]

I was wondering if there was an easy way to check if a change request has been re-scheduled?

I ask because I would like to generate some KPIs (Key Performance Indicators) based on the number of changes that have been re-scheduled, against the total number of changes over a time period. This way we can see if our re-schedule rate is changing over time and therefore make appropriate management decisions.

 

Thanks

[How can I find requests that are associated with a particular asset?]

Thanks to both of you for your replies and advice.

[How can I find requests that are associated with a particular asset?]

Is it possible to search for requests that are associated with an asset?

I ask this question because I am trying to create reports to find incidents, services requests etc. that are associated with a particular asset.

There must be an association because when you look at a particular request, you can see the assets associated with it. I am essentially trying to do the reverse of this. I've been looking at the various entities in the Entity Viewer to find a link but have been unsuccessful so far!

 

[Record States that are not 0, 1 or 2]

Excellent. Thanks for pointing this out.

[Record States that are not 0, 1 or 2]

Further to my query above, I ran the report again and found that a few more items were on the list compared to the last one. These assets were created recently.

I then had a go at adding an asset and found out that if I didn't enter a value for the 'Record State' then it defaulted to a value of -1.

Is it possible to set the 'Record State' field for an asset as mandatory so the person adding it has to set it to either 'Current' (0), 'Active' (1) or 'Archived' (2)?

This would force people to enter the value and allow us to monitor our assets better.

Thanks in advance

[Record States that are not 0, 1 or 2]

Okay, thanks for getting back to me.

[Record States that are not 0, 1 or 2]

I have been checking some assets by creating a report and found that 5 of the assets have a 'Record State' value of -1. I thought the 'Record State' values were either 0 (current), 1 (active) or 2 (archived).

Is this likely to be a hang-on from when records were copied over from the Windows version of SupportWorks to the web based one or does this '-1' state mean something else?

I am more curious than anything, but from an auditing point of view it would be good to know.

Cheers

Tom

[Question about font sizes in Widgets]

I did just notice that even if a datalist widget didn't scroll before as it was within the allocated space, once you have selected a scroll speed, it doesn't let you select no scroll speed.

[Question about font sizes in Widgets]

That's fantastic. it makes things a lot easier on the eye.

Cheers

Tom

[Is possible to re-direct to another URL when clicking on Widget]

This is just a quick query. Is it possible to redirect to another page when a widget is clicked on?

I'm was just curious because I have created a widget which shows the number of calls which have gone past their 'fix by' date, I also have a report which shows details about the calls that have past this date. it would be nice to be able click on the widget and have it redirect to the page where the report is generated.

I have a dashboard which shows several widgets counting figures like this and though that it would be nice have it act a bit like a 'command panel'.

Cheers

Tom

[Question about font sizes in Widgets]

Thanks, I've had a little experiment with this and it is looking very promising. As a suggestion it would be nice to have the ability to chose the speed at which they auto-scroll as this would make it easier to read the information shown.

[Reports using SQL Schema Designer with fixed date]

Thanks again Steve. I'll look at doing this instead!

[Reports using SQL Schema Designer with fixed date]

I've been trying to create a report using the SQL Schema Designer to return requests after a particular date (in this case 2018-09-01), but unfortunately I am not having any luck.

Could anyone offer any advice on how I could do this?