Widget Help

[Stephen.whittle]

Trying to create a widget which shows the current average ages of requests in days for all active requests in the system. I prefer to use measures for the widgets would anyone be able to help with the settings and query to achieve this. 

[Stephen.whittle]

@DanielRiare you able to help with this one please? 

[Hornbill Staff DR]

Hi @Stephen.whittle,

 thanks for your post.

I've had a look into this requirement and it isn't possible to get the output you need. To achieve this, the measure interface would need to allow us to obtain the average of a calculated value because the age of a request isn't something that's stored in the database. Storing such a value wouldn't be the done thing as age is a situation that changes as time passes and would be obtained by calculating the difference between the date the request was logged and now (the time that the report was run or the measure was sampled).

If you're exporting data out of Hornbill you can use the following query to get what you need:

SELECT 
    ROUND(AVG(DATEDIFF(NOW(), h_itsm_requests.h_datelogged)),1) AS Average_Days_Open
FROM h_itsm_requests
WHERE h_status NOT IN("status.resolved", "status.closed", "status.cancelled")

You can add criteria to the WHERE clause to get the average age of whatever set of requests you're interested in. The ROUND function is simply there to ensure the result is to a reasonable number of decimal places.

What we CAN achieve through a measure in Hornbill is a count of requests that are older than a certain number of days. Not exactly what you want but could be used to give an indication of improvement if the number of requests older than a certain number of months was decreasing. I'd set this number of months to the threshold that is unacceptable i.e. if you believe that the current state of your Service Delivery operations should mean that there should be no requests older than 3 months, then lets count how many requests are older than three months. The target would be to reduce the number of requests older than three months down to zero (or as close to zero as possible).

Now, I'm not saying this is a perfect substitute for monitoring the average age and therefore I've fed back the requirement to the Product Owner. While COUNTS are quite rudimentary, they still offer value in that they still allow you see some change and thus contribute to monitoring whether a situation is improving or not.

So, to create a measure that will sample the "No. of tickets older than 90 days" would be as follows:

Here's the WHERE clause for this COUNT: h_datelogged < DATE_SUB(CURDATE(), INTERVAL 90 DAY) AND h_status NOT IN ('status.resolved', 'status.closed', 'status.cancelled')

Other points to note are that the date ranging columns are empty, and I've specified some saved data columns.

The reason the date ranges are empty is because for the purposes of this measure we don't want to group the request records by any date value. i.e. it doesn't matter when the request was logged or resolved or closed. What we want is a snapshot of the number of active requests that are older than 90 days. I use the word "snapshot" because this situation will only be true at the point the sample is taken. It's worth noting that this configuration of measure can't be sampled retrospectively in that you can't go back and determine how many requests were older than 90 days at this time last month because the sample is taking a picture of what the data looks like at that point in time.

You'll notice that when you resample this measure for the first time, all the samples will be the same, I recommend setting the sample history to 1 the first time you do this. Once it's sampled, increase the sample history to a more appropriate amount and then let sample data build up naturally as per the frequency you're set (daily/weekly/monthly/whatever).

The saved data columns are useful when putting this measure in a widget as they'll show how many request are older than 90 days for each priority, team, or whatever saved data column you add. I'd use a chart-type widget set with a data type of "measure Samples group by".

I hope that helps, I appreciate that it's probably not exactly what you were after but may go someway to supporting the insight you're trying to gain into your requests.

Dan