Hello,

I have created a service desk report that shows various details including the comments but in some cases it  there is a long history of comments. My question is can you set it to only show the last X number of comments.

Thanks


2 Comments   [ + ] Show Comments

Comments

  • Can you post the SQL query for your report?
  • SELECT HD_PRIORITY.NAME AS PRIORITY, HD_TICKET.ID, HD_TICKET.TITLE, HD_STATUS.NAME AS STATUS_NAME,
    HD_TICKET.CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.CUSTOM_FIELD_VALUE8, HD_TICKET.MODIFIED,
    HDTC.COMMENT AS HD_TICKET_CHANGE_COMMENT_GROUPED FROM HD_TICKET
    JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
    JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
    JOIN (SELECT ID,HD_TICKET_ID,COMMENT,MAX(TIMESTAMP) FROM HD_TICKET_CHANGE WHERE (COMMENT != '' AND COMMENT NOT LIKE 'Machine %' AND COMMENT NOT LIKE '% was deleted.' ) GROUP BY ID ORDER BY ID DESC) HDTC ON (HDTC.HD_TICKET_ID = HD_TICKET.ID)
    WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((HD_STATUS.NAME != 'Resolved') AND (HD_STATUS.NAME != 'Withdrawn')) GROUP BY HD_TICKET.ID ORDER BY STATUS_NAME, HD_PRIORITY.ORDINAL, MODIFIED

    I have made some progress and can now see the last comment part but it no longer hyperlinks the call ref to the ticket. Appreciate any assistance

    Thanks
Please log in to comment

Answers

0
I have mine set up so that when the customer responds to a ticket it sends an email to the OWNER of the ticket and includes only the most recent comment.  I had to modify the SELECT statement to include

C.COMMENT

Then I had to add another JOIN statement:

JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                            AND C.ID=<CHANGE_ID>

Maybe that will work for you in whatever you have going already?
Answered 02/16/2015 by: Chris.Burgess
Orange Belt

Please log in to comment
Answer this question or Comment on this question for clarity