Changes between Version 15 and Version 16 of TracIniReportCustomFieldSample
- Timestamp:
- Apr 16, 2015, 8:27:22 PM (9 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
TracIniReportCustomFieldSample
v15 v16 1 = Report with TracTicketsCustomFields =1 = Report with TracTicketsCustomFields 2 2 3 3 Here are some examples of how to generate simple custom reports from TracTicketsCustomFields. 4 4 5 == Example 1 ==5 == Example 1 6 6 7 7 A report to show a Milestone summarize time inverted, new field test_one is Time to resolve a Ticket. 8 8 9 Enjoy! 9 '''Trac.ini:''' 10 {{{ 11 #!ini 12 [ticket-custom] 13 test_one = text 14 test_one.label = Inverted Time 15 }}} 16 17 '''Custom SQL:''' 18 {{{#!sql 19 SELECT 20 milestone AS Project, 21 SUM(p.value) AS Inverted_Time 22 FROM ticket t, ticket_custom p 23 WHERE p.ticket = t.id AND p.name = 'test_one' 24 GROUP BY milestone 25 ORDER BY milestone 26 }}} 27 28 == Example 2 29 30 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. 10 31 11 32 '''Trac.ini:''' 12 33 {{{ 13 14 [ticket-custom] 15 test_one = text 16 test_one.label = Inverted Time 17 18 }}} 19 20 '''Custom SQL:''' 21 {{{ 22 SELECT 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 35 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. 36 37 '''Trac.ini:''' 38 {{{ 39 34 #!ini 40 35 [ticket-custom] 41 36 charge = text … … 54 49 55 50 '''Custom SQL:''' 56 {{{ 51 {{{#!sql 57 52 SELECT DISTINCT 58 59 53 id AS ticket, 60 54 (CASE WHEN c.value ISNULL THEN '' ELSE c.value END) AS charge, … … 64 58 milestone AS customer, 65 59 summary, component, status 66 67 FROM ticket t,enum p 68 60 FROM ticket t, enum p 69 61 LEFT OUTER JOIN ticket_custom c ON 70 (t.id=c.ticket AND c.name='charge') 71 62 (t.id = c.ticket AND c.name = 'charge') 72 63 LEFT OUTER JOIN ticket_custom n ON 73 (t.id=n.ticket AND n.name='noncharge') 74 64 (t.id = n.ticket AND n.name = 'noncharge') 75 65 LEFT OUTER JOIN ticket_custom s ON 76 (t.id=s.ticket AND s.name='sitevisit') 77 66 (t.id = s.ticket AND s.name = 'sitevisit') 78 67 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 68 (t.id = o.ticket AND o.name = 'oohours') 69 WHERE p.name = t.priority 70 AND p.type = 'priority' 71 AND status = 'closed' 72 ORDER BY milestone 85 73 }}} 86 74 87 == Example 3 ==75 == Example 3 88 76 89 Going by the philosophy, you can never have too many examples, here's another one: 90 91 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%. 77 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%. 92 78 93 79 '''trac.ini:''' 94 {{{ 80 {{{#!ini 95 81 [ticket-custom] 96 82 progress = select … … 104 90 '''Custom SQL:''' 105 91 106 {{{ 92 {{{#!sql 107 93 SELECT p.value AS __color__, 108 94 id AS ticket, summary, component, version, milestone, severity, 109 95 (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner, 110 96 time AS created, 111 changetime AS _changetime, description AS _description, 97 changetime AS _changetime, 98 description AS _description, 112 99 reporter AS _reporter, 113 (CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS progress114 115 JOIN enum p ON p.name = t.priority AND p.type='priority'116 117 100 (CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS progress 101 FROM ticket t LEFT OUTER JOIN ticket_custom c ON t.id = c.ticket AND c.name = 'progress' 102 JOIN enum p ON p.name = t.priority AND p.type = 'priority' 103 WHERE status IN ('new', 'assigned', 'reopened') 104 ORDER BY p.value, milestone, severity, time 118 105 }}} 119 106 120 107 Notes 121 * the OUTER JOIN is the key to getting this to work122 * I'm not totally fluent in SQL, so this may not be the simplest way, but it works for me108 * The OUTER JOIN is the key to getting this to work. 109 * This may not be the simplest way, but it works for me. 123 110 124 == Example 4 == 125 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. 111 == Example 4 112 113 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. 126 114 127 115 '''trac.ini:''' 128 {{{ 116 {{{#!ini 129 117 [ticket-custom] 130 118 estimate = text … … 137 125 '''Custom SQL:''' 138 126 139 {{{ 127 {{{#!sql 140 128 SELECT 141 129 milestone AS Iteration, 142 130 SUM(estimate.value) AS Estimate, 143 131 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 132 FROM ticket t LEFT OUTER JOIN ticket_custom estimate ON (t.id = estimate.ticket AND estimate.name = 'estimate') 133 LEFT OUTER JOIN ticket_custom actual ON (t.id = actual.ticket AND actual.name = 'actual') 134 GROUP BY milestone 155 135 }}} 156 136 157 == Example 6 == 137 == Example 6 138 158 139 We wanted to add the ability to add a due date to a ticket. 159 Due dates can be added in either date format m/d/y or y/m/d 160 The report groups the tickets in three buckets: Due Today, Over Due, and Future 140 Due dates can be added in either date format m/d/y or y/m/d. 141 The report groups the tickets in three buckets: Due Today, Over Due, and Future. 161 142 162 143 '''trac.ini:''' 163 {{{ 144 {{{#!ini 164 145 [ticket-custom] 165 146 duedate = text … … 167 148 }}} 168 149 169 170 150 '''Custom SQL:''' 171 151 172 {{{ 152 {{{#!sql 173 153 SELECT p.value AS __color__, 174 154 (CASE 175 155 WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00', 176 STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE()156 STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE() 177 157 THEN 'Future Tickets' 178 158 WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00', 179 STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE()159 STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE() 180 160 THEN 'Today Tickets' 181 161 ELSE 'Over Due Tickets' 182 162 END) AS __group__, 183 163 id AS ticket, IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00', 184 STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) AS 'Due Date',164 STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) AS 'Due Date', 185 165 summary, component, version, milestone, t.type AS type, owner, status, 186 166 time AS created, changetime AS _changetime, description AS _description, 187 167 reporter AS _reporter 188 168 FROM (ticket t) 189 169 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' 190 170 LEFT JOIN ticket_custom d ON t.id = d.ticket AND d.name = 'duedate' 191 WHERE status <> 'closed' AND (STR_TO_DATE(d.value,'%Y/%m/%d') <> '0000-00-00'192 OR STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00')193 194 171 WHERE status <> 'closed' 172 AND (STR_TO_DATE(d.value,'%Y/%m/%d') <> '0000-00-00' OR STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00') 173 GROUP BY ticket 174 ORDER BY __group__ DESC, CAST(p.value AS signed), milestone, t.type, time 195 175 }}}