4 Replies Latest reply on Nov 29, 2013 10:56 AM by smiddleton

    Reporting on ticket times and other requested reports

    Apprentice

      My boss emailed me today asking if, by Monday, i could produce the following reports.

       

      Average time from Open to Resolve on an Incident.

      Average time from Resolve to Close on an Incident

      Number of tickets for each employee

      Number of closed tickets

      Number of Open Tickets

      5 oldest tickets

       

      Where do i even begin ? Does anyone here have reports like these ? Are these reports something that is taken care of in SQL ? Should i use a query or a report to collect this data ? Anyone have any ideas ?

       

      Thanks,

        • 1. Re: Reporting on ticket times and other requested reports
          Expert

          Average time from Open to Resolve on an Incident.

          If you add a calculation to find the difference in time, a standard query can do this report.  Otherwise use your favorite report writer.  If you don't have one, you don't have enough time to learn, add the calc.  There is a function to do time differences so its not bad.

           

          Average time from Resolve to Close on an Incident

          See above

           

          Number of tickets for each employee

          Standard query does this easy.  Group by analyst and in the criteria add "Creation Date" and make it user defined between dates.  This way you can enter any date range you want.  Or don't do user defined and set it at a fixed number like last 30 days.

           

          Number of closed tickets

          See above but no need to group.  Your criteria of status when  set to close over a period of time is interesting.

           

          Number of Open Tickets

          Same as above but instead of looking for status of Closed, look for NOT closed.

           

          5 oldest tickets

          Use the above incident, sort by oldest to newest on "Date Created" and then in the last screen you can limit it to "Return top 5 rows only" to get 5 oldest.

           

          If your going to do this often, create a short cut bar and all these reports to the short cut bar.  When you have done a bunch of queries, all of the above is about 30 minutes to an hour of work.  This is possible so quickly partially because in doing so many reports, you would have already added a calc to figure amount of time used in an incident.  While your at it, might want to add a % breach time calc, looks nice on the dashboards.

           

          To calculate % of time till breach: (V is number of minutes for a severity)

          import System

          static def GetAttributeValue(Incident):

            Value = 0

            V = 1

            X = 1

            if Incident.Severity.Title == '1':

             V = 120

            if Incident.Severity.Title == '2':

             V = 660

            if Incident.Severity.Title == '3':

             V = 1980

            if Incident.Severity.Title == '4':

             V = 3300

            if Incident.Severity.Title == '7':

             V = 21600

            X = Incident.MinutesToBreach()

            Value = 100-((X * 100) / V)

            return Value

           

          There are other useful built in functions for calculating time.  The guides on calcs do a good job of describe them in detail along with lots of other goodies.  If you have not used Boo before, the editor in 7.5 makes it much easier to keep alignment clear so I hope you have 7.5 or later..

          • 2. Re: Reporting on ticket times and other requested reports
            Apprentice

            We have used Reporting Services within SQL for this.  I had no skills on this about 3 weeks ago, but after learning how the DB worked I have been able to provide a very comprehensive repository of Management Reports.

             

            The queries work but doing it directly within SQL worked much better for me, and th Reporting Services solution allowed for reports to be designed in a much better way.

             

            Unfortuantely I am not sure that this actually helps you an awful lot, but I would sugget you speak with your DBA to see what they can do to help you setup Reporting Services and get to grips with it.  In 3 days I had this nailed (with no prior SQL knowledge) it just worked much better than the Crystal solution provided.

             

             

            Looking back at the reports you want these can be very quickly built as a query and added to a dashboard within WebDesk, the calculations are covered by the post above.  Its a very basic reporting solution with not a lot of scope for interigation of the data but in the short term would give your manager that information.  Could be an excuse for you to get a training course if nothing else?

            • 3. Re: Reporting on ticket times and other requested reports
              Expert

              Crystal Reports is the LANDesk provided report writer.  It can do just about anything.  However, very steep learning curve and logical and intuitive have no place in this software.  I just recently started using SSRS (SQL Server Reporting Services) and I created my 1st useful report in 2 days.  It's much more logical, intuitive, and very visual.  I am a big fan.  With Crystal Reports, every Google search returned as many answers as sited dedicated to how much people hate Crystal Reports.

              • 4. Re: Reporting on ticket times and other requested reports
                smiddleton Expert

                Crystal Reports is the LANDesk provided report writer.  It can do just about anything.  However, very steep learning curve and logical and intuitive have no place in this software.

                Steep doesn't even come close to describing the mountain you will start climbing with Crystal Reports

                It's logical and intuative if you live in a pit on the moon and believe the ground is the sky!

                 

                However it can produce some amazing reports once you have gotten to grips with it and if you spend long enough on the report to move that line just 1 more pixel into place

                 

                SQL Reporting Services provide a much quicker ability to grab a view into your data and look reasonable though you are fairly limited on design.