Ticket report on custom field "System Impacted"

[pryan67]

I'm trying to generate a report in LS Helpdesk which shows all tickets opened, along with date opened, assigned agent, agent team, Ticket Type, and a custom field called "System Impacted"

I can get everything except for "System Impacted". I can't seem to figure out how to list that custom field.

Here's what I have currently. Any help would be greatly appreciated:

Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
htbltickettypes.typename Type,
htblusers.name As AssignedAgent,
Count(htblticket.ticketid) As TicketCount
From htblticket
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Inner Join htblusers On htblusers.userid = htblagents.userid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Group By DatePart(yyyy, htblticket.date),
htbltickettypes.typename,
htblusers.name
Order By Year Desc,
Type,
AssignedAgent

[Rad_Tech]

I've taken a look at this as I needed this also for my new setup.
The custom field is located in htblticketcustomfield. Hope this is what you needed. Just change the highlighted areas to match your id's


Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
htbltickettypes.typename Type,
htblusers.name As AssignedAgent,
Count(htblticket.ticketid) As TicketCount,
htblticketcustomfield.data,
htblticketcustomfield.tickettypefieldid
From htblticket
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Inner Join htblusers On htblusers.userid = htblagents.userid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join lansweeperdb.dbo.htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Where htblticketcustomfield.tickettypefieldid = 45
Group By DatePart(yyyy, htblticket.date),
htbltickettypes.typename,
htblusers.name,
htblticketcustomfield.data,
htblticketcustomfield.tickettypefieldid
Order By Year Desc,
Type,
AssignedAgent