Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Pavol Procka
    Count of incidents per days without using a filter.Answered
    Topic posted March 9, 2018 by Pavol ProckaSilver Crown: 22,500+ Points 
    108 Views, 11 Comments
    Title:
    Count of incidents per days without using a filter.
    Content:

    Hi,

    I am trying to build a report that will show a combination of currently Open incidents in the Qs and the ones that were resolved "Yesterday" and "Today".

    I understand that to get the resolved on a specific date, I could make a simple count of e.g. incidents.last_ responded and add a filter, but then I will lose the incidents that are currently in Open status, so would have to go with a Dashboard combining reports.

    Is it possible to get this data in one report, ie with an expression in the column?

    Many thanks

    Pavol

    Best Comment

    Dev Aditya

    Pavol- there is a little correction in the expression-

    count(if(to_number(date_format(sysdate(), 'MMDDYYYY'))=to_number(date_format(incidents.closed, 'MMDDYYYY')), incidents.i_id))

    Comment

     

    • Dev Aditya

      Hi Pavol,

      Did you try 'Or' node in the filters. I believe it will make it easier to get data from both the filters. From columns, I am also not sure if we can do that.

    • Pavol Procka

      Hi Dev,

      If I use the filters, (even with OR) I will still end up with one column, which will be count of incidents resolved "yesterday" plus "today" / or am I missing something?

      What I need are three columns in one report:
      1: Count of Incidents resolved Yesterday
      2: Count of Incidents resolved Today
      3: Count of Open Incidents currently in the Q
       

       

    • Suresh Thirukoti

      Unless some reporting gurus correct me, we can do this via Custom scripts in reports....though on heavier side....we did for a customer recently who wasn't game for dashboards or multiple reports...

      - Create custom reports with appropriate filters and columns ...since its elapsed time, it should be Transaction table report of status changed (ID:6)

      - Via Connect PHP API analytics method, you can query it and place it in the report

      You can find examples in the forum on custom scripts with CPHP.....

      ~Suresh

    • Dev Aditya

      Hi Pavol,

      Please ignore last message if you received any notification mail

      Assuming you are using four columns

      1. Queue- incidents.queue_id

      2. Number of incidents closed today- count(if(date_diff(sysdate(),incidents.closed)<86400, incidents.i_id)))

      3. Number of incidents closed yesterday:  count(if(date_diff(sysdate(),incidents.closed)>86400 & date_diff(sysdate(),incidents.closed) < 172800 , incidents.i_id))


      4. Number of open incidents in the queue- count(if(incidents.status_id=1,incidents.i_id))

       

      I didn't test it rigorously, but, I guess it should work

       

      If you need hard and fast date based, then, custom scripting is the option I guess. :(

       

      Regards,

      Dev

    • Carl Elliott

      That has always been a limitation of the reporting, the filters will only work off of one date.  Dev's solution should work and if you wanted more flexibility with it you could put in variables for the system date but then you might need to bracket it on both sides of the date.    

      The OOTB solution would be to have a report for each and then combined them in a dashboard.  That is the simplest approach. 

    • Pavol Procka

      Hi Dev,

      I am trying to avoid the custom script. Your suggestion is really good, the only question I have outstanding is regarding point 2 :  Number of incidents closed today. Here I need all incidents that have been resolved so far since midnight of the current day (something like count (if incident.closed = sysdate()).

      Will the expression you provided not simply look at the last 24 hrs?

    • Suresh Thirukoti

      Thanks Carl for reminding the nicest takeway from MSE 2017 session.....Combining individual reports into a single report in Dashboards....Only limitation is that columns and types should be same..I think that's the case here...

      ~Suresh

    • Pavol Procka

      Thanks for your replies, but as said in the beginning, I am trying to achieve this in one report, not a dashboard which is the obvious solution :)

    • Dev Aditya

      Hi Pavol,

      I actually calculated on 24 hours basis. If you want it on same date, you can use-

      count(if(to_number(date_format(sysdate(), 'MONTHDDYYYY'))-to_number(date_format(incidents.closed, 'MONTHDDYYYY'))= 0, incidents.i_id))

      Same again, I didn't have it tested, but, I hope this logic makes sense.

       

      Regards,

      Dev

       

    • Dev Aditya

      Pavol- there is a little correction in the expression-

      count(if(to_number(date_format(sysdate(), 'MMDDYYYY'))=to_number(date_format(incidents.closed, 'MMDDYYYY')), incidents.i_id))

    • Pavol Procka

      Hi Dev and All,

      thanks for the help! And yes, it does work!

      Many thanks

      Pavol