/build/static/layout/Breadcrumb_cap_w.png

Custom reporting question on user field type

I've created a ticket queue for a team to identify colleague improvements needed.  I added a custom user type field they can search for the colleague identified instead of using and having to update a label.  Trying to create a weekly report of the tickets and the Colleague Name field shows as a number on the report.  What can I do to get the name to display on the report instead of the number?  I used the reporting wizard and this is the SQL

SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.SUMMARY, S.USER_NAME AS SUBMITTER_USER_NAME, S.FULL_NAME AS SUBMITTER_NAME, O.FULL_NAME AS OWNER_NAME, HD_TICKET.CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE2, HD_CATEGORY.NAME AS CATEGORY, GROUP_CONCAT(DISTINCT HD_TICKET_CHANGE.COMMENT SEPARATOR '\n') AS HD_TICKET_CHANGE_COMMENT_GROUPED, 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_VALUE5, HD_TICKET.CUSTOM_FIELD_VALUE1, HD_TICKET.CUSTOM_FIELD_VALUE8, HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.CUSTOM_FIELD_VALUE3, HD_TICKET.CUSTOM_FIELD_VALUE6, HD_TICKET.CUSTOM_FIELD_VALUE12, HD_TICKET.CUSTOM_FIELD_VALUE13, HD_TICKET.CUSTOM_FIELD_VALUE7, HD_TICKET.CUSTOM_FIELD_VALUE11  FROM HD_TICKET  LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 44)  GROUP BY HD_TICKET.ID ORDER BY ID


0 Comments   [ + ] Show comments

Answers (1)

Posted by: Hobbsy 3 years ago
Red Belt
0

You need to makes sure that the HD_TICKET table is joined to the USER table so you can select the username as a data field. I think that may be in your code at line 11, but you will also need to join your custom field in the same way so that you can then show the user name.

Another approach would be to format the custom field with an SQL statement that selects users that are in a label and shows the names, that way you already have the username in the HD_TICKET table already rather than the ID


Comments:
  • Thank you! - bfox81 3 years ago

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