Time to Resolution report (replacement for Incident Duration By Days report)

Version 11

    Environment: Service Desk 7.4 and Service Desk 7.5+

     

    Problem:

    The original Incident Duration By Days report made use of some custom crystal functions to calculate the business hours duration and clock stopped time for an incident.  This meant that there was a need to register CRUFL files on the local client machines.  This made it difficult to deploy the report and also made it slow to run.

     

    Solution:

    A new report has been produced which incorporates the main functionality of the original report.  Supplied along with the report are some database scripts that need to be run in order for it to work.  Steps to get the report working are below.  The main change in this report is that the calculations are done through the use of database server functions and so there is no need to do any additional client-side configuration for this to work.

    • One report supplied is in Crystal 2008 format for use with Service Desk 7.5 or higher.
    • The other report has been created in Crystal XI and tested against Service Desk 7.4.  It may also work on earlier versions.

     

    About the report:

    • The report is designed to work out the duration between the creation date and the latest resolution date.  If the incident has not been resolved yet it will not appear on the report.  If the incident has been resolved, reopened and then resolved again the report will calculate the duration between the creation date and the most recent instance that it was resolved.
    • If you want to keep an eye on incidents already open to ensure they don't stay open for too long you can do this using Response Levels (see your product manuals for more information) or you could have a calculation attribute showing the duration which you can place on queries.  Here is a document that tells you how: How to calculate the "business time" of an incident

     

    Steps to get the report working in SQL:

    1. Unzip the attached file.
    2. Within the TimeToResolution folder you will see 3 files ending *.sql.
    3. Within SQL Enterprise Manager connect to your Service Desk database within a query window then run Duration Functions.sql.
    4. Next run vw_Max_Resolution.  NOTE:  You may get a message to say that this view already exists, if so continue to step 5.
    5. Lastly run vw_IncidentDuration.sql.
    6. Then you will need to set the data locations on the report so that it connects to your database.  You can do this by following the instructions in this article: Configuring Crystal Reports with a Service Desk SQL Database

     

    Steps to get the report working in Oracle:

    1. Unzip the attached file.
    2. Within the TimeToResolution folder there is a subfolder called Oracle Views and Functions.zip (use these instead of those within the main folder).
    3. Connect to your Service Desk database and run Duration_Functions_Oracle.sql.
    4. Next run vw_IncidentDuration_Oracle.sql.  NOTE:  You may get a message to say that this view already exists, if so continue to step 5.
    5. Lastly run vw_Max_Resolution_Oracle.sql.
    6. Then you will need to set the data locations on the report so that it connects to your database.  You can do this by following the instructions in this article: Configuring Crystal Reports with a Service Desk ORACLE Database

     

    Additional step for use with 7.4:

    The report is currently hardcoded to have a timezone offset of +1 hour when displaying the incident creation date and the incident resolution date.  In order to amend this open the reports in Crystal XI report designer then amend the formulas CreationDateToLocalTime and ResolutionDateToLocalTime then alter the number 1 to be the offset from UTC(GMT) you require and save changes.

     

    NOTE (7.4 only): The formula in the report will not automatically adjust for daylight saving timezones.  If you have reports which already convert the timezone for you (such as incident.rpt for example) it would be better to check them and then copy the method used in these.  Just copy the formula and then change the field names to use in the Request report.  Alternatively here is an article showing you one method that you can use achieve this:

     

     

    Additional Information:

    After running these steps, you will now have a new table within your database called TimezoneInformation.  This holds the offset from UTC(GMT) and the time of the year that timezone changes occurr. This table should not need any adjustment at all, (it will automatically take into account daylight savings changes for example) however sometimes timezones will be change.  In order to understand the format of the rows in this table here is an example row:

     

    35,'Eastern Standard Time',-300,'2;1;3;02:00:00','1;1;11;02:00:00')

    • 35 is the Timezone Index and 'Eastern Standard Time' is the timezone name (these match the timezones specified against your Response level calendars in Service Desk).
    • The -300 is the offset from UTC(GMT) in minutes
    • '2;1;3;02:00:00' is the time when the timezone is in effect from.  The first number indicates the week of the month (in this case 2 indicates the 2nd week), the second number the day of the week (in this case1 which indicates Sunday), the third number is month of the year, and the last set is the time of the day that it takes effect.
    • '1;1;11;02:00:00' is the time when the timezone is no longer in effect.