Document usage of custom ticket fields in reports

[bryan@…]

TracTicketsCustomFields is great. However, on 0.8 I don't seem to be able to include the custom fields as columns in my report? Is this a limitation of Trac 0.8 or a limitation of my SQL-fu?

[bryan@…]

further info. System is Debian/stable with python 2.2.1, no mod_python. trac.ini contains:

[ticket-custom]
hours_est = text
hours_est.label = <i>Addtional</i> hours to complete
hours_est.value = 1
hours_spent = text
hours_spent.width = 5
hours_spent.label = Hours spent

And my test report contains:

SELECT p.value AS __color__,
   id AS ticket, summary, component, severity, owner, hours_est
  FROM ticket t, enum p
  WHERE status IN ('assigned') 
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY p.value

The report fails with the message

Report failed: no such column: hours_est

[eric.brown@…]

You're just using the wrong SQL. I've gotten it to work in trac 0.8. I think you need the following SQL:

SELECT p.value AS __color__,
   id AS ticket, summary, component, severity, owner, c.value AS hours_est
  FROM ticket t, enum p, custom_ticket c
  WHERE status IN ('assigned') AND t.id = c.ticket AND c.name = 'hours_est'
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY p.value

Now, this will only show tickets that have hours_est set in them. If that's all you want, you're set.

Or, if you want to show entries with hours_est and without:

   ...
   (CASE WHEN c.value IS NULL THEN '' ELSE c.value END) AS hours_est
  FROM ticket t LEFT OUTER JOIN ticket_custom c ON t.id = c.ticket AND c.name = 'hours_est'
     JOIN enum p ON p.name = t.priority AND p.type='priority'
   ...

Presumably if you want to show multiple custom columns, you can do additional LEFT OUTER JOINs and simply alias the ticket_custom table differently. I haven't tried that though.

I don't think this is a bug. Let me know if I mistyped any of the SQL above and you can't find the mistype. I use a custom column for the exact same purpuse you do and my query works fine. In fact, just in case my SQL above is wrong, here's the query I use on my system (my custom field is called duration):

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

[anonymous]

Ah, thanks. I bow to your greater SQL-fu. Knowing to pull from table custom_ticket is a huge help by itself! Being a newbie on linux and just a scratcher when it comes to SQL, I was stumbling around in the dark. Others with similar issues might be helped by taking a look at the ​database schema.

I suspect it would be good if someone documented an example or two on the TracTicketsCustomFields page or in the sample reports that come with Trac?

[jim@…]

I'm adding another example to the wiki, referenced off TracReports

[sid@…]

Okay, I added some of the comments from this ticket into the page TracTicketsCustomFields as well. Looks good guys.