Changes between Initial Version and Version 1 of TracIniReportCustomFieldSample

Show
Ignore:
Timestamp:
02/24/09 04:04:45 (6 years ago)
Author:
SamB (IP: 207.172.203.39)
Comment:

lifted off the trac trac

Legend:

Unmodified
Added
Removed
Modified
  • TracIniReportCustomFieldSample

    v1 v1  
     1= Report with TracTicketsCustomFields = 
     2 
     3Here are some examples of how to generate simple custom reports from TracTicketsCustomFields. 
     4 
     5== Example 1 == 
     6 
     7A report to show a Milestone summarize time inverted, new field test_one is Time to resolve a Ticket. 
     8 
     9Enjoy! 
     10 
     11'''Trac.ini:''' 
     12{{{ 
     13 
     14[ticket-custom] 
     15test_one = text 
     16test_one.label = Inverted Time 
     17 
     18}}} 
     19 
     20'''Custom SQL:''' 
     21{{{ 
     22SELECT  
     23   milestone as Project, 
     24   sum(p.value) as Inverted_Time 
     25   FROM ticket t, ticket_custom p 
     26   WHERE p.ticket = t.id and p.name = 'test_one' 
     27   group by milestone 
     28   ORDER BY milestone 
     29 
     30}}} 
     31 
     32 
     33== Example 2 == 
     34 
     35Here'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. 
     36 
     37'''Trac.ini:''' 
     38{{{ 
     39 
     40[ticket-custom] 
     41charge = text 
     42charge.label = Charge 
     43 
     44noncharge = text 
     45noncharge.label = Non charge 
     46 
     47sitevisit = text 
     48sitevisit.label = Site visit 
     49 
     50oohours = text 
     51oohours.label = Hours 
     52}}} 
     53 
     54 
     55'''Custom SQL:''' 
     56{{{ 
     57SELECT DISTINCT  
     58 
     59   id AS ticket, 
     60   (CASE WHEN c.value ISNULL THEN '' ELSE c.value END) AS charge, 
     61   (CASE WHEN n.value ISNULL THEN '' ELSE n.value END) AS nocharge, 
     62   (CASE WHEN s.value ISNULL THEN '' ELSE s.value END) AS sitevisit, 
     63   (CASE WHEN o.value ISNULL THEN '' ELSE o.value END) AS oohours, 
     64   milestone AS customer, 
     65   summary, component, status  
     66 
     67  FROM ticket t,enum p 
     68 
     69  LEFT OUTER JOIN ticket_custom c ON 
     70       (t.id=c.ticket AND c.name='charge') 
     71 
     72  LEFT OUTER JOIN ticket_custom n ON 
     73       (t.id=n.ticket AND n.name='noncharge') 
     74 
     75  LEFT OUTER JOIN ticket_custom s ON 
     76       (t.id=s.ticket AND s.name='sitevisit') 
     77 
     78  LEFT OUTER JOIN ticket_custom o ON 
     79       (t.id=o.ticket AND o.name='oohours') 
     80 
     81  WHERE (p.name=t.priority AND p.type='priority' AND status='closed') 
     82  
     83  ORDER BY milestone 
     84 
     85}}} 
     86 
     87== Example 3 == 
     88 
     89Going by the philosophy, you can never have too many examples, here's another one: 
     90 
     91I 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%. 
     92 
     93'''trac.ini:''' 
     94{{{ 
     95[ticket-custom] 
     96progress = select 
     97progress.label = Percent completed 
     98progress.options = 0|20|40|60|80|100 
     99progress.value = 0 
     100}}} 
     101 
     102Then to generate a simple report containing the new field, based on the default 'Active Tickets' report, I ended up with this: 
     103 
     104'''Custom SQL:''' 
     105 
     106{{{ 
     107SELECT p.value AS __color__, 
     108   id AS ticket, summary, component, version, milestone, severity, 
     109   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner, 
     110   time AS created, 
     111   changetime AS _changetime, description AS _description, 
     112   reporter AS _reporter, 
     113  (CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS progress 
     114  FROM ticket t LEFT OUTER JOIN ticket_custom c ON t.id = c.ticket AND c.name = 'progress' 
     115     JOIN enum p ON p.name = t.priority AND p.type='priority' 
     116  WHERE status IN ('new', 'assigned', 'reopened') 
     117  ORDER BY p.value, milestone, severity, time 
     118}}} 
     119 
     120Notes 
     121 * the OUTER JOIN is the key to getting this to work 
     122 * I'm not totally fluent in SQL, so this may not be the simplest way, but it works for me 
     123 
     124== Example 4 == 
     125I 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.  
     126 
     127'''trac.ini:''' 
     128{{{ 
     129[ticket-custom] 
     130estimate = text 
     131estimate.label = Estimate 
     132 
     133actual = text 
     134actual.label = Actual 
     135}}} 
     136 
     137'''Custom SQL:''' 
     138 
     139{{{ 
     140SELECT  
     141   milestone AS Iteration, 
     142   SUM(estimate.value) AS Estimate, 
     143   SUM(actual.value) AS Actual 
     144 
     145  FROM ticket t 
     146 
     147  LEFT OUTER JOIN ticket_custom estimate ON 
     148       (t.id=estimate.ticket AND estimate.name='estimate') 
     149 
     150  LEFT OUTER JOIN ticket_custom actual ON 
     151       (t.id=actual.ticket AND actual.name='actual') 
     152 
     153  GROUP BY milestone  
     154 
     155}}} 
     156 
     157== Example 5 == 
     158We 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. 
     159 
     160'''trac.ini:''' 
     161{{{ 
     162[ticket-custom] 
     163duration = text 
     164duration.label = Duration 
     165 
     166customer = text 
     167customer.label = Customer 
     168}}} 
     169 
     170This obviously didn't work: 
     171 
     172'''Custom SQL:''' 
     173 
     174{{{ 
     175SELECT  
     176  ticket_custom.value AS Duration 
     177 
     178  FROM ticket_custom 
     179 
     180  WHERE ticket_custom.name = 'duration' 
     181  AND ticket_custom.value = 'Our Customer' 
     182}}} 
     183 
     184What we did was make a "loop" INNER JOIN ticket_custom on ticket_custom.ticket like this: 
     185 
     186'''Custom SQL:''' 
     187 
     188{{{ 
     189SELECT  
     190  tc1.value AS Duration 
     191 
     192  FROM ticket_custom tc1 
     193 
     194  INNER JOIN ticket_custom tc2 
     195  ON tc1.ticket = tc2.ticket 
     196 
     197  WHERE tc1.name = 'duration' 
     198  AND tc2.value = 'Our Customer' 
     199}}}