locked
Working out SLA's taking into effect working hours and weekends RRS feed

  • Question

  •  

    Hi forum!

     

    I have been given the unenvious take of working out how to configure CRM to produce SLA reports for customers which take into account working hours and weekends.

     

    We use CRM in all aspects of our business, once of the main areas is on the helpdesk. We take calls and have response times and fix times for these calls along with priority levels. So if a call is taken and marked as priority 1 then we have 1 hour to reply to it and 4 hours to fix before the SLA for that customer is breached.

     

    The problem we are having is producing accurate reports for these SLA's. If, for example, we log a prioriy 3 job at 4PM we have 4 hours to respond, and since we are only open between 9AM and 5:30PM we have 1.5 hours that day and then 2.5 hours the next day to reply in - making the responce time 11:30AM the following day. How would we go about reporting this? And are there any functions within CRM which can calculate these 'working hours'?

     

    Currently we are using SQL Reporting Services to produce reports for our customers, but these reports do not take into account out-of-hours times and weekends - meaning a lot of the reports are showing the SQL's being breached when they are not.

     

    Any advice or guidence would be much appreciated.

     

    Thanks in advance

    Friday, October 10, 2008 3:11 PM

Answers

  • Hi Steve,

    What I've found from experience is that (in most cases) SLA's can be very difficult to track. Some customers require an initial incident to be followed up within X amount of time and that is all they need.  Usually this is where you receive a copy and paste to ask you some in depth questions barely dealing with your case at all.

    With more complex SLA's, a case may be resolved and the customer believes their issue has been solved, but two days later the same problem crops up and the initial case is re-activated... Auditing usually allows for retrospective analysis of whether SLA's were met. You would know best which category your company falls into. Unfortunitely Auditing is not standard with MS CRM, but you can write your own auditing solution or buy 3rd party add-on's.

    Either way, let me know how you get on, will be interested to hear what works for you.

    Cheers,

    Karlo







    Monday, October 13, 2008 5:28 PM

All replies

  • Hi SProvis,

    Yes, it is a pretty complex task!

    We produced a product for managing SLA's  (http://www.ver206.com/tracker.html) in detail as some clients need in depth tracking of incidents and total durations at a particular status in real-time.

    The easiest way of achieving your task is full auditing. Essentially audit every important status change/update using post callouts. This will enable you to determine the period of time between status changes and calculate total time at a particular stage. This is usually sufficient for reporting. Public holidays are particularly difficult to calculate, but working hours/weekends are achievable. It's also fairly simple to determine the day of week in SQL.

    Where you need alerts when a case is about to exceed it's SLA you would need a windows service to monitor the current status's and update where appropriate.


    Good luck!

    Karlo




    Friday, October 10, 2008 6:01 PM
  • There is no out of the box function in SQL or CRM for calculating business days between two dates, but my colleague Jason Farmer has probably the best (most simple) solution that I have seen.

     

    http://blog.customereffective.com/blog/2008/07/calculating-the.html

     

    This will get you part of the way, you will then just need to work out the hours.  This could probably be expanded to consider hours, not days.

     

    Sunday, October 12, 2008 6:52 PM
    Moderator
  • Hi Karlo,

     

    Thanks for the reply - I've looked at the tracked link, looks good so may try and convince my manager to go down that route.

     

    In the mean time I'm still toying with ideas how to do this - full auditing sounds like it could work, would this need to be enabled within CRM? (Sorry I'm very new with CRM and been thrown into the deep end here).

     

    The alert side of things I can do, I have already created a wallboard within SQL reporting services which outputs this information (again I need to work out working hours for these wallboards as any job that lasts over a day is showing up the incorrect values).

     

    Steve

    Monday, October 13, 2008 3:36 PM
  •  

    Thanks for the reply, looks interesting. I way toying with a similar script created in JS but that was causing me all sorts of problems as it didn't seem to react to weekends.

     

    This works and is pretty simple to implement - I'll look at how to implement hours into this (or re-write it accordingly).

     

    Any help would be appreciated, my SQL is very rusty.

     

    Steve

    Monday, October 13, 2008 3:39 PM
  • Hi Steve,

    What I've found from experience is that (in most cases) SLA's can be very difficult to track. Some customers require an initial incident to be followed up within X amount of time and that is all they need.  Usually this is where you receive a copy and paste to ask you some in depth questions barely dealing with your case at all.

    With more complex SLA's, a case may be resolved and the customer believes their issue has been solved, but two days later the same problem crops up and the initial case is re-activated... Auditing usually allows for retrospective analysis of whether SLA's were met. You would know best which category your company falls into. Unfortunitely Auditing is not standard with MS CRM, but you can write your own auditing solution or buy 3rd party add-on's.

    Either way, let me know how you get on, will be interested to hear what works for you.

    Cheers,

    Karlo







    Monday, October 13, 2008 5:28 PM