/build/static/layout/Breadcrumb_cap_w.png

Reporting on Custom values with User

Hello, I am new to SQL and trying to create a report based on a new ticket queue.

The queue has a Custom_3 value as Colleague Name and set to a User select.  Owner and Submitter are already in use and this is a separate user field the submitter can select.  When I create a report based on this queue using the Wizard and select Custom_3 it only displays the ID value for the user instead of Full Name.  What do I need to add to join to the User table to pull that info into the report?

This is the current SQL statements for the report.  Any help is appreciated.

SELECT HD_TICKET.ID,
HD_TICKET.CREATED,
HD_TICKET.TITLE,
HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.SUMMARY,
HD_TICKET.CUSTOM_FIELD_VALUE0,
HD_TICKET.CUSTOM_FIELD_VALUE2,
IF(HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.IS_PARENT, HD_SERVICE_STATUS.NAME, HD_STATUS.NAME) AS STATUS_NAME, HD_TICKET.CUSTOM_FIELD_VALUE3, O.FULL_NAME AS OWNER_NAME, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.CUSTOM_FIELD_VALUE8, HD_TICKET.CUSTOM_FIELD_VALUE5, HD_TICKET.CUSTOM_FIELD_VALUE1, HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.CUSTOM_FIELD_VALUE7, HD_TICKET.CUSTOM_FIELD_VALUE11, HD_TICKET.CUSTOM_FIELD_VALUE6, HD_TICKET.CUSTOM_FIELD_VALUE12, HD_TICKET.CUSTOM_FIELD_VALUE13, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED, O.USER_NAME AS OWNER_USER_NAME, HD_TICKET.RESOLUTION, HD_TICKET.CUSTOM_FIELD_VALUE14  FROM HD_TICKET  
LEFT JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.HD_SERVICE_STATUS_ID and HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID
LEFT JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 44) AND ((HD_TICKET.CREATED < now() ))  GROUP BY HD_TICKET.ID ORDER BY ID


0 Comments   [ + ] Show comments

Answers (1)

Posted by: JasonEgg 3 years ago
Red Belt
1

Add this to your joins:

LEFT JOIN USER C ON (C.ID = HD_TICKET.CUSTOM_FIELD_VALUE3)

Then add this to the select clause:

C.FULL_NAME as COLLEAGUE_NAME,

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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