Helpdesk Reporting Question

I am creating a custom report for tickets grouped by site (custom field) with Comments as one of the columns. The problem is the comments column only shows the first comment. How would I get all the comments listed under a ticket without listing the ticket several times?

Thank for the help.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

1
You will have to customize the SQL of the query to get what you want and use a GROUP_CONCAT function on all the COMMENT fields. COMMENT is a row in the HD_TICKET_CHANGE table. For each ticket there are many comments.

Here's a basic version
SELECT HD_TICKET.ID, O.FULL_NAME AS OWNER_NAME, S.FULL_NAME AS SUBMITTER_NAME,
GROUP_CONCAT(COMMENT ORDER BY CAST(HD_TICKET_CHANGE.ID AS CHAR) DESC SEPARATOR '\n--------\n')
COMMENTS

FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID =
HD_TICKET.HD_CATEGORY_ID)
JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN USER U ON USER_ID=U.ID
LEFT JOIN USER S ON S.ID=HD_TICKET.SUBMITTER_ID
WHERE HD_TICKET_CHANGE.OWNERS_ONLY=0
GROUP BY HD_TICKET_ID
ORDER BY HD_TICKET.ID desc

for a more complete version see the FAQs: [link]http://www.kace.com/support/customer/faq/index.php?action=artikel&id=954[/link]
Answered 11/04/2009 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
1
Thanks for the info. With a little playing around I was able to figure it out. But, now I am getting multiple separators at the end of the comments (see attachment). Why do they show up and is there a way to get rid of them? Trying to condense the report as much as possible. Thanks again.

Answered 11/06/2009 by: zpizzullo
Senior Yellow Belt

Please log in to comment
1
The blank lines represent "description" updates (i.e. owner change or other field change... not a traditional "Comment"). These are represented in the tickets as bulleted items. Each of these entries has a blank Comment field, so that is why you cannot see the data in your report. I'll dig in and post a solution if I come up with one. Ideally, the report would show the Description field for each item when the Comment field is empty.
Answered 11/23/2009 by: airwolf
Tenth Degree Black Belt

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