11-29-2022 05:52 PM - last edited on 04-01-2024 01:30 PM by Mercedes_O
I'm trying to modify the report Helpdesk: Time worked and create two reports both filtering the tickets by a custom field named "StonePanels Ticket" which is a radio button yes/no. I want it to only show tickets that have that field marked yes but can't seem to find what table to pull that from.
One report I need to show the last year, and the other the last months tickets. Both should only be closed tickets. Any help pointing me in the right direction for the ticket custom field would be appreciated.
11-29-2022 09:36 PM
Is there a way to filter tickets to the "last month" instead of x number of days?
11-29-2022 09:18 PM
Well I've made some progress but am just getting stuck adding the column to this report to show time worked.
Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblticket.subject As Subject,
Max(htblhistory.date) As Closure
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Inner Join htblhistory On htblticket.ticketid = htblhistory.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Inner Join htblticketstates htblticketstates1 On
htblticketstates1.ticketstateid = htblhistory.ticketstateid
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblticketstates.statename = 'Closed' And htblticket.spam <> 'True' And
htblhistorytypes.name In ('Status changed', 'Note added and state changed',
'Internal note added and state changed') And htblticketstates1.statename =
'Closed' And htblticketcustomfield.data = 'yes' And htblcustomfields.name =
'StonePanels Ticket'
Group By htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar),
htblticket.date,
htblticket.updated,
htbltickettypes.typename,
htblticketstates.statename,
htblpriorities.name,
htblsource.name,
htblusers.name,
htblusers1.name,
htblticket.subject,
htblusers2.name
Having Max(htblhistory.date) > GetDate() - 30
Order By Closure Desc,
htblticket.ticketid
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now