Reporting - SLA's

[AndyColeman]

Good afternoon,

From February 1st we are going to start officially using SLA's for our incidents and service requests. Over the last few months, the team have been getting used to the system but now will have SLA's to keep up to date. 

I have set SLA's up in Service Manager, no problems at all, my query is however what is the most efficient way of doing a report for individual and team SLA's met? 

There is nothing obvious I could see. I would appreciate any feedback on how to efficiently report month by month on success rates for SLA's met. 

Many thanks in advance. I attach a picture of an example SLA we have. 

Andy 

[Martyn Houghton]

@AndyColeman

From a reporting purpose the main indicators whether a request is or was completed within SLA, the two main indicator fields in the system are h_withinfix and h_withinresponse which indicate if the request was completed within fixed and response SLA  retrospectively.

https://wiki.hornbill.com/index.php/Table_Info:_Main_Request_Table

I would recommend you start with reports based on the Request Entity rather than jumping straight into the more complex SQL report options. 

Cheers
Martyn

 

[AndyColeman]

Thanks Martyn. I will give it a go. 

Appreciated,

Andy

[AndyColeman]

@Martyn Houghton

I am struggling to see if I can create a report to give me a simple 'User A - Completed 85% of successful responses/resolves' 

Using the above I seem to be listing each ticket with a '1-0-(null)' response. 

Is there any other information you can provide at all please? 

[Guest]

Hi @AndyColeman

If you are looking to use Percentages over counts, this could potentially be achieved by creating a measure for every analyst individually - and displaying them on a Measure Samples chart as a number of Series. 

For example, based on your requirement above, the measure I created below shows the percentage of requests that met their resolution target, resolved by grahamc - month by month. 

 

You could then create a number of measures, by copying these and changing just the h_resolvedby_user_id for your other analysts and compare them next to eachother in a widget. 

If you were happy to use counts as opposed to percentages you could create a single measure to firstly see how many requests were resolved on a monthly basis, but also add a "Saved Data Column" for the "Resolved By Username" - then when you create your widget, you could group the results based on this. Unfortunately this method does not work for percentages

 

I hope this helps!

 

Kind Regards

 

Bob

 

[AndyColeman]

Hi @Bob Dickinson

I have run the measure like above, for myself, I know I hit 100% of my SLA's for Feb but the measure is identifying 15.5449 oddly. 

Is this me doing something wrong? 

Any help would be greatly appreciated. 

[Guest]

Hi @AndyColeman

The measure is behaving correctly - but I think you are wanting it to display something a little different to what I originally anticipated. 

Your measure is in effect set up to say "Of all of the requests that were within fix last month, what percentage of them were resolved by Andy". 

So if you had 200 requests that were within fix last month, and Andy resolved 40 of them, the percentage that would come back would be 20%

 

But based on your recent message, I think what you want it to show is "Of all requests that were resolved by Andy last month, what percentage of them were within fix"

So if you resolved 40 requests last month, and they were ALL within fix, the percentage that would come back would be 100%. 

To achieve this, simply swap the criteria in your Query Where Clause and Percentage Sub Query and it should return as expected. 
 

I hope this all makes sense, let me know how it goes!

Regards

Bob

[AndyColeman]

Hi @Bob Dickinson

 

Thanks for your help. Could you just please confirm what I should be using in the Query Where Clause and Percentage Sub Query fields please?

Thanks,

Andy

 

 

[AndyColeman]

Apologies @Bob Dickinson if there is any further info you could provide before Wednesday that would be a massive help for the report I am doing.

 

Thanks. 

[Guest]

Hi @AndyColeman

Based on the above screenshots, I'd suggest the following should be used:

Query Where Clause: h_resolvedby_user_id = 'colemana'

Percentage Sub Query: h_withinfix = 1

Give it a try and see if it gives the results you expect. 

Kind Regards

Bob

 

[AndyColeman]

Hi @Bob Dickinson

Fab, thanks. That looks a lot better. 

Really appreciate the help.

Just to clarify one thing. If when looking at Feb's stats for example, does this measure includes things that were logged before Feb that I resolved in Feb that would have potentially already had breaches? I imagine so, just wanted to clarify. If so, is there a way I can tell the measure to ignore anything logged before the month I am reporting on?

 

Thanks again.

[AndyColeman]

Sorry @Bob Dickinson I have been asked to do something else for this report. Is there any way I can break this down further by showing the percentages of how many 'High Incidents' , 'Medium Incidents' , 'Low Incidents' and 'Service Requests' were met? 

 

So for example if I had 96% of my SLA's met for February could I break that down to say 30% of those were high incidents. 

Thanks.

[Guest]

Hi @AndyColeman

In answer to your first question, you could base the date range on the Date Logged date instead of the Date Resolved date (simply change the Date Ranging Column to "Date Logged"). Though I'm thinking you may miss information here?

Example - Your working hours are 9am until 5pm - A call is logged at 4.30pm on February 28th with a 8 hour resolution target. Over night, that months samples run - this call is rightly not included, as its not been resolved. You resolve it at 9.15am the next day - so you have met the resolution target. However in next months stats, as we are looking at the Date Logged, this fix would not been included as it was not Logged and Resolved within the same month. 
If this suits your requirements, then please feel free to give it a try. 

In answer to your second question. I have investigated but I don't think its currently possible to perform a sub-grouping on a percentage measure (I'm currently asking this question internally just to make sure that's correct)
As an alternative, you could set up something a bit like I have below. This is a SQL Group By widget - so its not using a measure, and therefore will not give you a month-on-month comparison. But if you are looking for last weeks/last months/last years stats like this its quite easy to make. The figures within the bars are the actual number of requests rather than percentages - but you can see the split based on the 100% bar at the bottom.

If this interests you, I can post the configuration to set something like this up?

 

Kind Regards

 

Bob

[AndyColeman]

Hi @Bob Dickinson

Yes please. That would be great if you could post the configuration. 

 

Really appreciate all the help.

Andy

[Guest]

Hi @AndyColeman

This a Data Chart Widget

The first part of the config:

 

• The Filter is: 

h_fk_priorityname!='' and h_ownername!='' and (h_status = 'status.resolved' or h_status = 'status.closed') and h_withinfix = 1

• The "Manage Series" section has four entries added - one for each Priority in my system (Major, High, Medium, Low). As you can see, once you have used the "Add New" button, you just add in the relevant sub-filter and a colour. This is how the bars are split into the different priorities. 
• You can also change the Date Range using the Sample Period option

 

The design options:

 

I hope this helps!! 

Kind Regards

 

Bob

[AndyColeman]

Thanks @Bob Dickinson

This looks really good. 

Only problem is I only get the option for Major in the Manage Series option.

The way our priorities are laid out are seen in the following screenshots.  Would it need a different field? 

Thanks,

Andy

 

[Guest]

Hi @AndyColeman

"Major" is just an example on my own instance. Its an example of a Priority.

You have 3 Incident related priorities, so for you, your would add 3 Series:

1)
Series Name: 2 Hours (High Incident)
Series Filter: h_fk_priorityname = '2 Hours (High Incident)'
Colour: Red

2)
Series Name: 1 Day (Medium Incident)
Series Filter: h_fk_priorityname = '1 Day (Medium Incident)'
Colour: Yellow

3) 
Series Name: 5 Day (Low Incident)
Series Filter: h_fk_priorityname = '5 Day (Low Incident)'
Colour: Green

Kind Regards

Bob

[AndyColeman]

Thanks for all your help @Bob Dickinson I THINK I am getting somewhere now. I am sure you will be relieved to hear!

 

I have created two widgets. One for SLA's met like your example above and one other for SLA's not met (I just changed the FILTER to h_withinfix = 0 which I assume is correct). I have used LAST MONTHS SAMPLES as my sample period to hopefully show accurate stats for February.  

The only query that is left for me now is checking the numbers are accurate. According to the screenshots below I only resolved 38 tickets last month, all within fix time however if I run my report I did resolve 101 tickets. So I am just wondering where those missing tickets are. Am I missing a filter perhaps? 

 

Again appreciate the help.

Thanks!

Andy

 

[Guest]

Hi @AndyColeman

What is the filter that you are using on the report (to generate the graph showing 101)?

My guess is that this could be a conflict between the Date Logged/Date Resolved criteria that we discussed earlier on that thread. If you post a screenshot or send me the report definition, I can take a look. 

Kind Regards

Bob

 

[AndyColeman]

Hi @Bob Dickinson please find report definition attached.

I know I did not have 70 tickets in my queue from before February which is what concerns me with the accuracy. I would estimate I probably closed about 10-20 of pre February calls last month.

Thanks, 

Andy

 

total-resolved-between-date-range.report (1).txt

[Guest]

Hi @AndyColeman

Its quite difficult to pin point specific count issues without having access to your data set. This will all come down to a data issue - something missing from a column that you would expect to be populated, or a count being performed on the wrong component. 

The best way to do this is to review the raw data itself - you can use Database Direct, but its also easy to do via another report. Please see the attached report which should extract the key attributes that we are discussing. This is based on all calls resolved by you over a date range that you specify - I'm specifically interested in the log/resolve dates, and what appears in the "Within Resolve Time" Column. We can then count up what we would expect to appear.  

You may be able to work out where the problem is by looking at these results - or if you want to private message me the result HTML, I will take a quick look for you. 

Kind Regards

Bob

all-andys-calls.report.txt

[Guest]

Hi @AndyColeman

As discussed after a closer inspection of your data, the reasons the counts were not showing as expected was because Service Requests were included and they were being logged against a different Priority. 

The choices here were to either amend the filter to exclude Service Requests, or add an additional Sub Series for Priority. Either way, this will then provide an accurate count of all of the data. 

I hope this has helped!

Kind Regards

Bob

[AndyColeman]

Thanks! This worked like a dream. Appreciate the help.