Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Sasi Gudimetta
    How to backlog and find when an incident was assigned to an...Answered
    Topic posted September 25, 2016 by Sasi GudimettaSilver Trophy: 7,500+ Points 
    237 Views, 5 Comments
    Title:
    How to backlog and find when an incident was assigned to an agent
    Content:

    Hi All,

    I have a reporting query.

    My requirement is to find the count of assignments of incidents that were assigned to a particular agent on a backdated time.

    For example: Let's say an incident was created on 1st of August and assigned to Agent 1 on the same day. This assignment happened through 'Fill Inbox' so basically it means that Agent 1 has assigned the case to himself.

    Now on 2nd of August Agent 1 has manually assigned the incident to Agent 2. This is a manual assignment done to Agent 2 by Agent 1.

    This data should be shown no matter when i run the report from August 3rd on-wards.

    I tried to create the report using the transactions table. Transaction type = 4 is 'Assigned'.

    But because of this what happens is both assignment transactions fall under Agent 1's account because the actual Assignment transaction was done by Agent 1.

    My output should basically be like

    Date - Agent Name - Number of Assignments

    August 1 - Agent 1 - 1

    August 2 - Agent 2 - 1

    Was wondering if anyone can guide me with this requirement.

    Many thanks,

    Sasi K Rao.

     

    Version:
    Service Cloud Feb'16

    Best Comment

    Bastiaan van der Kooij

    Sasi,

    that's when you want an outer join (an outer join does not force both sides of the join to have data). Just outer join the accounts table to the transactions table. Just make sure that when you add a filter to the accounts table you leave the option to have null values as well, since otherwise you would effectively kill the outer join.

    Bastiaan

    Comment

     

    • Bastiaan van der Kooij

      hey Sasi,

      for assigned transactions (type = 4), the account that got the object assigned is stored in id2. Just join the accounts table to the transactions table and you see the option to join on ID2 whereby type = 4

      You can find this information by browsing through the Data Dictionary under Configuration and looking at the transactions table.

      astiaan

    • Sasi Gudimetta

      Hi Bastiaan,

      That works. Many thanks.

      I have a sub query to this. Now in the same report i would like to show the number of responses, the incidents that were closed by the advisor and how many were in pending status at that point of time.

      If i join the accounts table with transactions.id2 then the other columns data does not show up because this link is very specific to the incident getting assigned to the agent and not related to the other transactions.

      Can you suggest how i can find a remedy to this.

      I've attached a report definition that I tried creating but did not work out successfully.

      Many thanks for your help,

      Regards,

      Sasi K Rao.

    • Bastiaan van der Kooij

      Sasi,

      that's when you want an outer join (an outer join does not force both sides of the join to have data). Just outer join the accounts table to the transactions table. Just make sure that when you add a filter to the accounts table you leave the option to have null values as well, since otherwise you would effectively kill the outer join.

      Bastiaan

    • Sasi Gudimetta

      Hi Bastiaan,

      Thanks for that. Duly noted. :)

      Will try the same and see if it works out.

      Regards,

      Sasi K Rao.

       

    • Vinod kumar

      Hi Sasi,

      Thanks for your information above.

      I am stuck here,my incidents was assigned to 3 mem I mean total assigned for a case is 3,how to pu out the names of the agents who are assigned with this case.

      Thanks in advance.

      R.Vinod Kumar