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.

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • 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.
Please log in to comment

Answers

0
I was able to figure it out. I ended up using a third party mysql editor to play with the join until I got it. Final result is as follows in case anyone is interested.

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
Answered 04/02/2015 by: POB Technology
Senior White Belt

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