This is a public Forum  publicRSS

Topic

    Ziv S
    Duplicate rows issue in reportAnswered
    Topic posted June 11, 2018 by Ziv SExplorer, last edited June 11, 2018 
    70 Views, 7 Comments
    Title:
    Duplicate rows issue in report
    Content:

    Hi everyone,

    We are working on a report that will show the number of assigned agents per agent and per queue. Our agents can receive incidents from multiple queues when filling inbox, and we'd like to show each agent in a single row, even if incidents from different queues are assigned to the agent.

    The problem I'm facing is with an aggregate column that's meant to show the longest 'pending' incident in the agent's inbox, without creating additional records/rows per queue. I'm attaching a screenshot to illustrate the issue. For Thomas for example, only one row should show: Time in Inbox 7h 38m + the number of assigned incidents per Q.

    Is there a way to show 'Time in Inbox' information only once per agent in this scenario? We are not really interested to see this information per queue and the additional rows can be confusing.

    I have also attached the report definition to this thread.

    Much appreciated,
    Ziv

    Image:

    Best Answer

    Sebastiaan

    I will attach some instructions with pictures in pdf that should get you going. It's a combination of group_concat and custom script.

    If you are completely new to custom scripts then you can have a look at this post first
    A 'How to' on using regular expressions in custom scripts for GDPR

    Answer

     

    • Sebastiaan

      You already have the correct syntax for your "time in inbox" max(date_diff(sysdate(),incidents.updated)/60)

      It's having data in your other columns that create a row for each data object. (they are all unique, there is no duplicate row)

    • Sebastiaan

      I think you will have to experiment with the group concat function
      See: Functions

      group_concat(expr, separator[, sort column 1, ASC|DESC][, sort column 2, ASC|DESC][,...])

      This function allows you to combine multiple values from data set expr into a delimited list in a single row. The list is delimited by separator and ordered by the specified sort columns. For example, the expression group_concat(threads.note, ',', threads.entered, ASC) returns a comma-delimited list of thread notes ordered by time entered ascending.

    • Colby Ross
      How many queues do you have? If you don't have very many or they could be separated out by group, you could make a column for each queue and do something like: count(distinct if(incidents.queue_id = 8, incidents.i_id)). This would give you the agent and then put all queues on a single row with their counts. Then your max calculation would be off all incidents assigned to the agent and a single row value. It is not ideal, but could be a short term solution to get you that data you need for now.
    • Sebastiaan

      I will attach some instructions with pictures in pdf that should get you going. It's a combination of group_concat and custom script.

      If you are completely new to custom scripts then you can have a look at this post first
      A 'How to' on using regular expressions in custom scripts for GDPR

    • Ziv S

      Hey @Sebastiaan,

      I really appreciate the information you sent regarding group concat function. I tried to do a basic expression for grouping the agent/time in inbox columns; it looks like it's not possible to use an aggregate function within the group_concat function (see attachment).

      I played a bit with the Cross Tab options under the Display tab in the report editor and got to a satisfactory result. I can't find a way to add additional images, but basically the result is that per each queue, we have 2 sub-columns with # of incidents and time in box, and the agent name showing only once. I also shortened the column names to simply 'Incidents' and 'Time' which made the report a lot more compact in terms of horizontal scrolling.

      Regardless, I will have to play a bit with the group_concat function as well as report scripts which I have yet to use to see how they can be used outside of this specific scenario.

      Thank you for your your answers everyone!

      Best,
      Ziv

    • Sebastiaan

      You can keep the expression you originally had in your 'Time in inbox' max(date_diff(sysdate(),incidents.updated)/60)

      Also the last column count(distinct(incidents.i_id)) is fine and does not require any changes

      For the queue you can use the group_concat and custom script. You do need to adjust the queue names in the script as to get a match.
      If you can give me the names of your queues then I can adjust the custom script for you and upload a complete report for you.

    • Sebastiaan

      I forgot to mention that queue abriviations can be done in the custom script.

      So, let's say you have a queue called Escalation Queue - English
      Then you could have an abriviation called Escl. EN in your custom script as followed

      $Q1 = preg_match_all("/(Escalation Queue - English)/", $rows[0][2]->val);
      $Q2 = preg_match_all("/(French)/", $rows[0][2]->val);
      $Q3 = preg_match_all("/(English G2)/", $rows[0][2]->val);
      $rows[0][2]->val = 'Escl. EN='.$Q1.'     FR='.$Q2.'     EN G2='.$Q3;