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 = htblcustomfields.fieldid Inner Join (Select Top 1000000 htblticketcustomfield.data, Sum(htblnotes.timeworked) As MinutesWorked From htblticketcustomfield Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid Inner Join htblnotes On htblnotes.ticketid = htblticketcustomfield.ticketid Inner Join htblhistory On htblticketcustomfield.ticketid = htblhistory.ticketid Inner Join htblticketstates On htblticketstates.ticketstateid = htblhistory.ticketstateid Inner Join htblticket On htblticket.ticketid = htblticketcustomfield.ticketid Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.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