Report with TracTicketsCustomFields

Here are some examples of how to generate simple custom reports from TracTicketsCustomFields.

Example 1

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

Trac.ini:

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

Custom SQL:

SELECT 
   milestone AS Project,
   SUM(p.value) AS Inverted_Time
FROM ticket t, ticket_custom p
WHERE p.ticket = t.id AND p.name = 'test_one'
GROUP BY milestone
ORDER BY milestone

Example 2

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 custom field in turn.

Trac.ini:

[ticket-custom]
charge = text
charge.label = Charge

noncharge = text
noncharge.label = Non charge

sitevisit = text
sitevisit.label = Site visit

oohours = text
oohours.label = Hours

Custom SQL:

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

Example 3

I added a custom ticket field to provide an idea of the percentage of the ticket completed so far. This is a simple dropdown list where the developer can log their progress to the nearest 20%.

trac.ini:

[ticket-custom]
progress = select
progress.label = Percent completed
progress.options = 0|20|40|60|80|100
progress.value = 0

Then to generate a simple report containing the new field, based on the default 'Active Tickets' report, I ended up with this:

Custom SQL:

SELECT p.value AS __color__,
   id AS ticket, summary, component, version, milestone, severity,
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, 
   description AS _description,
   reporter AS _reporter,
   (CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS progress
FROM ticket t LEFT OUTER JOIN ticket_custom c ON t.id = c.ticket AND c.name = 'progress'
   JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened')
ORDER BY p.value, milestone, severity, time

Notes

• The OUTER JOIN is the key to getting this to work.
• This may not be the simplest way, but it works for me.

Example 4

I have added a few custom fields to track roughly the amount of work spent on each ticket. One field is the estimated work (in days) and the other is the actual work (in days). I need a simple report to summarize the total amount of work for each milestone: estimated and actual.

trac.ini:

[ticket-custom]
estimate = text
estimate.label = Estimate

actual = text
actual.label = Actual

Custom SQL:

SELECT 
   milestone AS Iteration,
   SUM(estimate.value) AS Estimate,
   SUM(actual.value) AS Actual
FROM ticket t LEFT OUTER JOIN ticket_custom estimate ON (t.id = estimate.ticket AND estimate.name = 'estimate')
   LEFT OUTER JOIN ticket_custom actual ON (t.id = actual.ticket AND actual.name = 'actual')
GROUP BY milestone

Example 6

We wanted to add the ability to add a due date to a ticket. Due dates can be added in either date format m/d/y or y/m/d. The report groups the tickets in three buckets: Due Today, Over Due, and Future.

trac.ini:

[ticket-custom]
duedate = text
duedate.label = Due Date

Custom SQL:

SELECT p.value AS __color__, 
   (CASE 
     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00', 
              STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE()
          THEN 'Future Tickets' 
     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00', 
              STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE() 
          THEN 'Today Tickets' 
          ELSE 'Over Due Tickets' 
    END) AS __group__,
   id AS ticket, IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00', 
   STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) AS 'Due Date', 
   summary, component, version, milestone, t.type AS type, owner, status,
   time AS created, changetime AS _changetime, description AS _description,
   reporter AS _reporter
FROM (ticket t)
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_custom d ON t.id = d.ticket AND d.name = 'duedate'
WHERE status <> 'closed' 
  AND (STR_TO_DATE(d.value,'%Y/%m/%d') <> '0000-00-00' OR STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00')
GROUP BY ticket
ORDER BY __group__ DESC, CAST(p.value AS signed), milestone, t.type, time