| 69 | |
| 70 | == Example 3 == |
| 71 | |
| 72 | Going by the philosophy, you can never have too many examples, here's another one: |
| 73 | |
| 74 | 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%. |
| 75 | |
| 76 | So in my trac.ini I added: |
| 77 | |
| 78 | {{{ |
| 79 | [ticket-custom] |
| 80 | progress = select |
| 81 | progress.label = Percent completed |
| 82 | progress.options = 0|20|40|60|80|100 |
| 83 | progress.value = 0 |
| 84 | }}} |
| 85 | |
| 86 | Then to generate a simple report containing the new field, based on the default 'Active Tickets' report, I ended up with this: |
| 87 | |
| 88 | {{{ |
| 89 | SELECT p.value AS __color__, |
| 90 | id AS ticket, summary, component, version, milestone, severity, |
| 91 | (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner, |
| 92 | time AS created, |
| 93 | changetime AS _changetime, description AS _description, |
| 94 | reporter AS _reporter, |
| 95 | (CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS progress |
| 96 | FROM ticket t LEFT OUTER JOIN ticket_custom c ON t.id = c.ticket AND c.name = 'progress' |
| 97 | JOIN enum p ON p.name = t.priority AND p.type='priority' |
| 98 | WHERE status IN ('new', 'assigned', 'reopened') |
| 99 | ORDER BY p.value, milestone, severity, time |
| 100 | }}} |
| 101 | |
| 102 | Notes |
| 103 | * the OUTER JOIN is the key to getting this to work |
| 104 | * I'm not totally fluent in SQL, so this may not be the simplest way, but it works for me |