Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    JLFolkman
    Report to include backlog, cases created and cases closed in...Answered
    Topic posted March 11, 2015 by JLFolkman Gold Trophy: 10,000+ Points 
    304 Views, 13 Comments
    Title:
    Report to include backlog, cases created and cases closed in last 7 days
    Content:

    Is it possible to have one report to display the case backlog, the number of cases created in the last 7 days and the cases closed in the last 7 days.

    Example:

    Open Cases Cases Created Last Week Cases Closed Last Week
    1 0 1
    10 2 2

    Best Comment

    Mike Griffiths

    Also, just a quick and related aside, it's also possible to create a report that allows the user to enter a date range and then choose which field to filter on. In this example, I've used Incidents Date Created and Incidents Date Closed, though the principle can be applied to any record that has two dates.

    Hope this helps!

    Regards,

    Mike

    Comment

     

    • Bhagwan Singh Mer

      For 'Open'/'Created'/'Closed' over a date range, wouldn't the canned report (AcId=85) suffice? 

       

    • JLFolkman

      This is a great report but doesn't allow me to add fields from the Org table. I was able to create a report that provides the information needed.

    • Bhagwan Singh Mer

      Thats Great! Did you join stats>interfaces>incidents table?

      Would you mind sharing the same?

    • Ryan Schofield

      i'd like to see too if you don't mind sharing. never could get something set up for this so had to use dashboard

    • Charlie Mopps
      JLFolkman said:

      This is a great report but doesn't allow me to add fields from the Org table. I was able to create a report that provides the information needed.

      View original

      You need to copy the report and save it somewhere else to edit it.

    • JLFolkman

      Attached is the report definition. I had to hard code Created and Closed but am now being asked for a way to allow a date range to be entered for the cases created and cases closed.

      Any ideas?

    • Ryan Schofield

      Create a variable (or 2) then use that your formula.. So "$date1" and "$date2" could be your variables for the 'start' and 'end' of the time period. Then you'd have two date_diff functions, comparing incidents.created to $date1 and then to $date2, using an "AND" operator to ensure the date is between the two. Or I guess you could just do $date1<incidents.created and incidents.created<$date2. I don't recall what I tried in the past but as I could never get it to work properly it's probably worth your fresh eyes on it.

      Though your users would need to understand that the "Open Cases" column, based on your current definition, won't give you the backlog at that point in time but the current backlog.

    • Bhagwan Singh Mer

      Jerry,

      I had to hard code Created and Closed but am now being asked for a way to allow a date range to be entered for the cases created and cases closed

      I imported your report def but not sure if I understood it clearly especially what you meant by "hardcode created and closed...". An example would help 

      Thanks,

      MB

    • Charlie Mopps

      MB said:


      Jerry,




      I had to hard code Created and Closed but am now being asked for a way to allow a date range to be entered for the cases created and cases closed




      I imported your report def but not sure if I understood it clearly especially what you meant by "hardcode created and closed...". An example would help 



      Thanks,



      MB


      View original


      You replied to the wrong post. JLFolkman was the one that gave you the report ;-)

    • Mike Griffiths

      Hi JLFolkman,

      To answer your question (and to expound on Ryan's post a little), it is possible to create a report that will show you any Incidents created or closed within a given timeframe. I'd detail how this is done, but it's far easier to attach an example report definition for you to review/use. Note that if you wish to add Backlog to this report, you'll need to remove the date filters and add the following expression into another column:

      sum(if(incidents.status_id != 'Solved', 1,0))

      Hope that helps!

      Regards,

      Mike

    • Mike Griffiths

      Also, just a quick and related aside, it's also possible to create a report that allows the user to enter a date range and then choose which field to filter on. In this example, I've used Incidents Date Created and Incidents Date Closed, though the principle can be applied to any record that has two dates.

      Hope this helps!

      Regards,

      Mike

    • Bastiaan van der Kooij

      JLFolkman,

      getting all three values in one report is difficult and you might also consider this to be not correct. The dates in this report are referring to an interval: 24 hours in which incidents are closed or created. The backlog is always about a specific point in time.

      The easiest way to create a report that shows both created and solved within a timeframe, grouped per interval is to use the transactions table. I attached the definition for this. In this report there is the option to count every unique solve or only the final solve (as incidents can be solved multiple times). You can also add the updated count from customers and responses in this report so you would have #created, #updated, #solved, #responses.

      For Backlog you can use the Servicing interval in a seperate report.

      Bastiaan

    • JLFolkman

      This report was exactly what I needed:

      Incidents_Created_AND_Closed_in_date_100799.xml

      I tweaked to include a few additional fields but it is working as desired.

      Thanks Mike!