Reporting against the group that the incident was assigned to when it breached

Version 5

    Environment: Tested on Service Desk 7.4 and 7.5

     

    What and why?

     

    Customers have been asking about an easy way to be able to report upon which assignment was the active one when an incident breached.  Here I am going to outline some simple design changes that make this easier.

     

    How it works:

     

    Firstly you need to add in a database view which will select only the last assignment made before your incident breached and then the Crystal report will total this by Group.  The view was kindly shared by a community member on this thread: Querying breach time and breached group

     

    This is how the View works:

    1. It gets a list of assignments created before their Incident breach time "dbo.im_incident.im_breach_time > dbo.pm_process_assignment.pm_creation_date" Where the Incident has breached "dbo.im_incident.im_is_breached = 1"
    2. It then groups these assignments by Process GUID "GROUP BY pm_process_guid" Return the newest assignment for each Process using the assignment serial number "MAX(AssignmentSerial) AS AssSerial"
    3. This will then bring back a list of process GUIDs and Assignment serial numbers which are used within the report attached.

     

    In order to get this working:

     

    • Test this out on your test system to ensure this is working as expected and take a full backup before implementing on live.
    • There is a flag on the incident table called is_breached.  This should set to True (1) when the incident breaches.  This is set when an escalation point associated with the incident has the is_breach flag set on it and this breaches.  So in order for this to work ok you need to ensure that all your escalation points except for the one which you want to indicate an incident breach, do not have this flag set on it.  Typically this means that you ensure your escalation points (accessed via Administration - Response Levels) do not have the flag ticked unless it is the 100% escalation of your overall SLA.
    • Once the is_breach flag is being set correctly the im_breach_time will also be recorded for datetime that the is_breach escalation point breached.
    • The next step is to run the vw_breachgroup sql against your SQL database in order to create the database view.
    • I have attached a Crystal report which will show breached incidents by the group that the incident was assigned to when it breached (one report is for 7.4 and the other for 7.5).  The report for 7.4 should also work against earlier versions of Service Desk but has not been specifically tested.
    • In order to get the Crystal report to run you need to point it to your database and in order to do this follow these steps: Configuring Crystal Reports with a Service Desk SQL Database
    • If using this with 7.4 (or earlier) you will need to edit the @CreationDateToLocalTime formula in order to set your own timezone offset.