Aaah, ok so there is no functionality for this in the current OTRS user interface, but I will have to write my custom web page.
I will try that. Thanks for such a quick response J Olli From: [email protected] [mailto:[email protected]] On Behalf Of Frans Stekelenburg Sent: 12 May 2009 11:11 To: User questions and discussions about OTRS. Subject: Re: [otrs] How to create a report on time used Hi Olli, Maybe not a direct answer to your question, but here three examples I am working with. They may give you a clue. The second one might even be close to your solution (Sum( t.time_unit )) #1 time spent per agent SELECT t.create_by, u.first_name, u.last_name, Sum(t.time_unit) FROM time_accounting AS t Left Join users AS u ON t.create_by = u.id WHERE year(t.create_time) = '2009' GROUP BY t.create_by #2 tickets created in March 2009 for customer FOOBAR SELECT t.ticket_id, c.tn AS 'NSC#', c.freetext1 AS 'CustRef', c.title AS Title, tt.name AS TYPE , c.ticket_priority_id AS 'Prio', c.customer_id AS 'Customer', c.freetext11 AS Site, Sum( t.time_unit ) AS min, concat( floor( Sum( t.time_unit ) /60 ) , ':', mod( Sum( t.time_unit ) , 60 ) ) AS mytime FROM time_accounting AS t LEFT JOIN ticket AS c ON t.ticket_id = c.id LEFT JOIN ticket_type AS tt ON c.type_id = tt.id WHERE year( t.create_time ) = '2009' AND month( t.create_time ) = '3' AND c.customer_id = 'FOOBAR' GROUP BY t.ticket_id LIMIT 0 , 30 # 3 report on all tickets closed in 3/2009 of customer 'FOOBAR' that are NOT Changes This one has a little bug somewhere, I think merged tickets get counted double. SELECT tn as 'Case#',t.freetext1 as 'CustRef', t.title as Title,t.freetext11 as Site, tt.name as Type,q.name as 'Queue',tp.name as 'Prio', t.create_time as Opened, th.create_time as Closed, ts.name as State, u.first_name as Engineer, Sum( ta.time_unit ) AS 'real min', concat( floor( Sum( ta.time_unit ) /60 ),':', lpad(truncate(mod(sum(ta.time_unit),60),0),2,0) ) AS 'real time', t.customer_user_id as 'Requester' FROM `ticket_history` as th LEFT JOIN ticket AS t ON th.ticket_id = t.id LEFT JOIN ticket_priority as tp ON th.priority_id = tp.id LEFT JOIN ticket_type as tt ON t.type_id = tt.id LEFT JOIN ticket_state as ts ON th.state_id = ts.id LEFT JOIN time_accounting as ta ON t.id = ta.ticket_id LEFT JOIN users as u ON th.owner_id = u.id LEFT JOIN queue as q ON t.queue_id = q.id where (th.history_type_id = 27 and th.state_id in (2,3,10) and year(th.create_time) = 2009 and month(th.create_time) = 3) AND customer_id = 'ACME' AND tt.name != 'Change' GROUP by t.tn Any corrections are welcome :=) gr, Frans From: [email protected] [mailto:[email protected]] On Behalf Of Olli Aro Sent: dinsdag 12 mei 2009 11:46 To: [email protected] Subject: [otrs] How to create a report on time used Hi all, Does anyone know how I can compile a list of fix time used to fix tickets for all tickets in a single queue? I use field "Time units (work units)" in order to record time for every ticket, but now it would be useful to be able to see somehow the sum of time for all tickets. Regards, Olli
--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs NEW! ENTERPRISE SUBSCRIPTION - Get more information NOW! http://www.otrs.com/en/support/enterprise-subscription/
