/build/static/layout/Breadcrumb_cap_w.png

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
  • Can you post the SQL query for your report? - chucksteel 9 years ago
  • 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 - garyafarmer 9 years ago

Answers (1)

Posted by: Chris.Burgess 9 years ago
Orange Belt
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?
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ