Service Desk report with last x number of comment only
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
Answers (1)
Please log in to answer
Posted by:
Chris.Burgess
9 years ago
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?
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 - garyafarmer 9 years ago