I would like to render a report which shows our 'Root cause' trends from Lansweeper for a given timeframe (30, 60 and 90 days). The root cause data is required to close all tickets and can be one to three fields depending on the issue. The values were created under 'Ticket custom fields and currently I am only able to list the 'Root cause category field and not any subsequent sub fields, i.e root cause: Printing, sub-fields: Drivers, Network printer, Office printer, Scan/copy, Toner. From the code listed, I'm not able to find or list the sub-fields values from Custom Field data. Any help would be greatly appreciated.
30 Day Report:
Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblusers1.name As AssignedTech,
htblusers.name As [User],
htbltickettypes.typename As Type,
htblticketcustomfield.data As RootCause,
htblticket.subject As Subject
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 htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Where htblticket.date > GetDate() - 30 And htblticket.spam <> 'True' And
htblticketcustomfield.fieldid = '2'
Order By RootCause Desc
