Custom User field not pulling name in reports.
Hi I was hoping someone could help me with a report issue I'm having. So, we have decided we wanted to use a custom field that pulls in the Users Full Name within our tickets instead of using Submitter. So far it has worked fine until I try to run reports on that custom field and it does not pull the Full Name into the report. It pulls the user ID Number. Below is the SQL Code for my report. I made the report using the Report Wizard because I know nothing about SQL. Any help would be greatly appreciated.
SELECT HD_TICKET.CUSTOM_FIELD_VALUE3, O.FULL_NAME AS OWNER_NAME, HD_CATEGORY.NAME AS CATEGORY, HD_TICKET.TITLE, HD_TICKET.CUSTOM_FIELD_VALUE4, HD_TICKET.CUSTOM_FIELD_VALUE0, HD_TICKET.CUSTOM_FIELD_VALUE1 FROM HD_TICKET
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND ((date(HD_TICKET.CREATED) >= cast(date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3) month) as datetime) and date(HD_TICKET.CREATED) < date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3)+3 month))
AND (HD_STATUS.NAME like '%closed%'))
ORDER BY OWNER_NAME, CUSTOM_FIELD_VALUE3
As an FYI the Field that is not reporting correctly is HD_TICKET.CUSTOM_FIELD_VALUE4 I believe. It is the custom field that pulls our user info into the ticket. Like I said earlier it is not displaying the User Name in the report as it does in the tickets. Thanks again for your time.
-
I think I need to do a JOIN of somekind of CUSTOM_FIELD_VALUE4 to the my user table's full name, but that's where I'm stuck. - POB Technology 9 years ago
Answers (1)
Select
ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 As TICKET_DATE,
USER1.FULL_NAME As USER,
ORG1.HD_CATEGORY.NAME As CATEGORY,
ORG1.HD_TICKET.TITLE,
ORG1.HD_STATUS.NAME As STATUS,
ORG1.HD_TICKET.CUSTOM_FIELD_VALUE0 As LOCATION,
ORG1.HD_TICKET.CUSTOM_FIELD_VALUE1 As DEPARTMENT,
ORG1.USER.FULL_NAME As OWNER
From
ORG1.HD_TICKET Left Join
ORG1.USER On ORG1.USER.ID = ORG1.HD_TICKET.OWNER_ID Left Join
ORG1.USER USER1 On USER1.ID = ORG1.HD_TICKET.CUSTOM_FIELD_VALUE4 Inner Join
ORG1.HD_CATEGORY On ORG1.HD_CATEGORY.ID = ORG1.HD_TICKET.HD_CATEGORY_ID
Inner Join
ORG1.HD_STATUS On ORG1.HD_STATUS.ID = ORG1.HD_TICKET.HD_STATUS_ID
Where
ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 >= Cast(Date_Add(Date_Sub(CurDate(),
Interval DayOfYear(CurDate()) - 1 Day), Interval ((Quarter(CurDate()) - 1) *
3) Month) As datetime) And
ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3 < Date_Add(Date_Sub(CurDate(), Interval
DayOfYear(CurDate()) - 1 Day), Interval ((Quarter(CurDate()) - 1) * 3) + 3
Month)
And
ORG1.HD_TICKET.HD_QUEUE_ID = 1
And
ORG1.HD_STATUS.NAME Like '%closed%'
Order By
OWNER,
ORG1.HD_TICKET.CUSTOM_FIELD_VALUE3,
ORG1.HD_TICKET.CUSTOM_FIELD_VALUE4