Report with CustomFields

This is a simple Custom Report with Custom Ticktes Fields.

A report to show a Milestone summarize time inverted, new field test_one is Time to resolve a Ticket.

Enjoy!

Trac.ini

[ticket-custom]
test_one = text
test_one.label = Inverted Time

Custom SQL

SELECT 
   milestone as Proyectos,
   sum(p.value) as Tiempo_Invertido
   FROM ticket t, ticket_custom p
   WHERE p.ticket = t.id and p.name = 'test_one'
   group by milestone
   ORDER BY milestone

BTW.Sorry my English and short text.

---

Here's another longer example … I have four fields in my ticket_custom, and I want them all to be seen in the report. If the existing field was a NULL, the report code adds the word "None" to the output, so I'm getting rid of that with a CASE statement. The main magic is the series of LEFT OUTER JOINs, which let me access each field of ticket_custom in turn.

SELECT DISTINCT 

   id AS ticket,
   (CASE WHEN c.value ISNULL THEN '' ELSE c.value END) AS charge,
   (CASE WHEN n.value ISNULL THEN '' ELSE n.value END) AS nocharge,
   (CASE WHEN s.value ISNULL THEN '' ELSE s.value END) AS sitevisit,
   (CASE WHEN o.value ISNULL THEN '' ELSE o.value END) AS oohours,
   milestone AS customer,
   summary, component, status 

  FROM ticket t,enum p

  LEFT OUTER JOIN ticket_custom c ON
       (t.id=c.ticket AND c.name='charge')

  LEFT OUTER JOIN ticket_custom n ON
       (t.id=n.ticket AND n.name='noncharge')

  LEFT OUTER JOIN ticket_custom s ON
       (t.id=s.ticket AND s.name='sitevisit')

  LEFT OUTER JOIN ticket_custom o ON
       (t.id=o.ticket AND o.name='oohours')

  WHERE (p.name=t.priority AND p.type='priority' AND status='closed')
 
  ORDER BY milestone