Reporting and Analytics for Service

Get Involved. Join the Conversation.

Topic

    Pavol Procka
    Reporting various Incident Thread Types in one cellAnswered
    Topic posted August 1, 2018 by Pavol ProckaSilver Crown: 22,500+ Points, last edited August 1, 2018 
    67 Views, 13 Comments
    Title:
    Reporting various Incident Thread Types in one cell
    Content:

    Hi,

    Can anyone help with the expression for the column that would output multiple thread entry types in one cell?

    I. e. I need to have a cell that contains all the text from all threads in the incident.

    if I simply make a column with threads.note I only get the text of the initial customer query but not the auto-response, staff responses and private notes.

    When I do to_char(threads.note) I get all entries but on a separate line.

    The business case here is to enable agents to quickly view the incident through the "More" link in the report rather than having to open and close the incident in a separate tab.

    Many thanks

    Pavol

    Version:
    18A

    Best Comment

    Justin

    use group_concat() instead

    Comment

     

    • Justin

      use group_concat() instead

    • Pavol Procka

      Thx Justin, exactly what I needed.

    • Pavol Procka

      Would you know if it is possible to incorporate a line break into the expression? So that I can have each thread start on a new line within the More window?

    • Justin

      ive never tried using line break but i think it should work in theory, heres the whole function layout

       

       

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

    • Justin

      i personally normally just use, group_concat(threads.note, '//', threads.entered, ASC)

    • Pavol Procka

      I went with group_concat(threads.note, '***NEW THREAD***', threads.entered, DESC) as per your first reply, but am not sure what to do with the second one :(

      What do I need to replace "separator" with? Do I also have to replace "sort column 1" etc with something?

      I tried like this but it does not work :)

      group_concat(threads.note, '***NEW THREAD***',[, sort column 1, DESC][, sort column 2, DESC])

    • Justin

      try this

       

      group_concat(threads.note, '***NEW THREAD***',threads.entered, ASC)

    • Justin

      it puts the thread.notes seperated by '***NEW THREAD***' and in the ascending order that the threads were entered.

    • Pavol Procka

      Thanks again, I will go with it. Would be sweeter if I could make it separate with an empty line, but don't want to be too much of a pain :)
      Btw. I will go with DESC as that is the order we prefer :)

    • Justin

      Im fairly certain you could accomplish seperate lines with a custom script inside the report, it would take me forever to figure out how though.

    • Sebastiaan Draaisma

      group_concat(to_char(threads.note), '\n\n\n')



      See this post from Gursimran https://cloudcustomerconnect.oracle.com/posts/17dbc8d183







      Try this definition for Thread Note column: group_concat(to_char(threads.note), '\n\n\n')



      The \n puts a line break.



      Remember to update the Character limit of this column to 300000 (which is the max).





    • Pavol Procka

      Hi Sebastiaan,

       

      Thanks again so much. Would change your answer to best but it somehow evades me how I can change it.

      Have a nice day!

      Pavol

    • Jess Campbell

      Sebastiaan said:


      group_concat(to_char(threads.note), '\n\n\n')



      See this post from Gursimran https://cloudcustomerconnect.oracle.com/posts/17dbc8d183







      Try this definition for Thread Note column: group_concat(to_char(threads.note), '\n\n\n')



      The \n puts a line break.



      Remember to update the Character limit of this column to 300000 (which is the max).






      View original


      Awesome!