→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper
Hi there,

I need help in regards of creating a new custom report.
I already have a Report which shows how much time has been "worked" put in on Tickets with custom Fields. In my case we have a Custom Field "Category" which we categorize the Tickets in, for example: VPN, Office365, User, Hardware etc.

But this report only shows the total Time worked which has been put in to these categories but I want a second one to only consider the last 7 days and if you're really good another one for "this month".

I can post my current Report in here maybe it's easy for someone to just add a couple things and it's done, I've tried it but it always gave me Error's in the Report.

My Report:

Select Distinct Top 1000000 htblticketcustomfield.data As Category,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) %
60))) + ' minutes' As TimeWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblticketcustomfield.fieldid =
Inner Join (Select Top 1000000 htblticketcustomfield.data,
Sum(htblnotes.timeworked) As MinutesWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
Inner Join htblnotes On
htblnotes.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistory On htblticketcustomfield.ticketid =
Inner Join htblticketstates On htblticketstates.ticketstateid =
Inner Join htblticket On
htblticket.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid =
Where htblticketstates.statename = 'Closed' And 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
htblcustomfields.name Like '%Category%'
Group By htblticketcustomfield.data
Having Max(htblhistory.date) > GetDate() - 120) As Custom1 On
Custom1.data = htblticketcustomfield.data