Helpdesk Reports with Custom Fields

[scarsysadmin]

I am working on a report that will pull certain info from each ticket. In my case, I want the building and the issue category. Both of those fields are helpdesk custom fields. I've got the report to the point where I get the info I want, but each ticket has two rows. One row has the building and the other has the issue category. What can I do to have the report have one row per ticket with the data I need?

Thanks for any insight

P.s. This is my current query created by using the gui

Select Distinct Top 1000000 htblticket.ticketid,
htblticketcustomfield.ticketid As ticketid1,
htblticket.subject,
htblticket.date,
htblcustomfields.name,
htblticketcustomfield.data
From htblticket
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where (htblcustomfields.fieldid = 54) Or
(htblcustomfields.fieldid = 58) Or
(htblcustomfields.fieldid = 42)
Order By htblticket.ticketid

[scarsysadmin]

This is extremely helpful and gives me plenty to work with for other reports. Thank you!

[Nick_VDB]

We did some modifications to the query by making use of sub-queries so that each custom field gets their own column.

Select Distinct Top 1000000 htblticket.ticketid,
htblticket.subject,
htblticket.date,
CustomField1.name As Name1,
CustomField1.data As Data1,
CustomField2.name As Name2,
CustomField2.data As Data2,
CustomField3.name As Name3,
CustomField3.data As Data3
From htblticket
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblcustomfields.name,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblticketcustomfield.fieldid = 54) As CustomField1
On htblticket.ticketid = CustomField1.ticketid
Inner Join htblnotes On htblticket.ticketid = htblnotes.ticketid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblcustomfields.name,
htblticketcustomfield.fieldid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblticketcustomfield.fieldid = 58) As CustomField2
On htblticket.ticketid = CustomField2.ticketid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblcustomfields.name,
htblticketcustomfield.fieldid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblticketcustomfield.fieldid = 42) As CustomField3
On htblticket.ticketid = CustomField3.ticketid
Order By htblticket.ticketid

[scarsysadmin]

Until I get this figured out I'll have to run multiple reports and combine them in excel I guess.