cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
pryan67
Champion Sweeper II
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

1 REPLY 1
Rad_Tech
Engaged Sweeper
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