Report with TracTicketsCustomFields
Here are some examples of how to generate simple custom reports from TracTicketsCustomFields.
A report to show a Milestone summarize time inverted, new field test_one is Time to resolve a Ticket.
[ticket-custom] test_one = text test_one.label = Inverted Time
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
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 custom field in turn.
[ticket-custom] charge = text charge.label = Charge noncharge = text noncharge.label = Non charge sitevisit = text sitevisit.label = Site visit oohours = text oohours.label = Hours
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
Going by the philosophy, you can never have too many examples, here's another one:
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%.
[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:
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
- the OUTER JOIN is the key to getting this to work
- I'm not totally fluent in SQL, so this may not be the simplest way, but it works for me
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 iother is the actual work (in days). I needed a simple report to summarize the total amount of work for each milestone: estimated and actual.
[ticket-custom] estimate = text estimate.label = Estimate actual = text actual.label = Actual
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
We wanted to query all custom_ticket.value of a certain type (custom_ticket.name), where a different custom_ticket.value of the same ticket was met.
[ticket-custom] duration = text duration.label = Duration customer = text customer.label = Customer
This obviously didn't work:
SELECT ticket_custom.value AS Duration FROM ticket_custom WHERE ticket_custom.name = 'duration' AND ticket_custom.value = 'Our Customer'
What we did was make a "loop" INNER JOIN ticket_custom on ticket_custom.ticket like this:
SELECT tc1.value AS Duration FROM ticket_custom tc1 INNER JOIN ticket_custom tc2 ON tc1.ticket = tc2.ticket WHERE tc1.name = 'duration' AND tc2.value = 'Our Customer'