/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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   [ - ] Hide 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.
Answer this question or Comment on this question for clarity

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