Changes between Version 3 and Version 4 of DygraphsVisualizationPlugin
- Timestamp:
- Jun 22, 2015, 7:14:11 PM (9 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
DygraphsVisualizationPlugin
v3 v4 54 54 == Example graphs 55 55 56 === Report the number of open tickets with particular values of a custom field over time56 === Report the history of tickets open and closed 57 57 58 58 It's often useful to be able to generate reports that span a period of time, for monitoring the progress of a project. 59 59 60 Constructing such reports is not always straightforward. Here's an example of a report that looks for tickets of type 'fault' that have a custom field 'safety_related'. At any time, this field can have one of three values that classify the safety status of the ticket: 'Not assessed', 'Safety related' or 'Not safety related'. The report plots the count of the number of open tickets with each safety status at the start of every week for the past three years.60 Constructing such reports is not always straightforward. Here's an example of a report that plots the number of tickets that were opened and closed throughout the duration of the project. It also plots the growth of the "backlog": the number of currently-open tickets at that time. 61 61 62 62 Note that this query uses Common Table Expressions. These have only been present in SQLite since [[http://www.sqlite.org/releaselog/3_8_3.html|version 3.8.3]]. It will usually be necessary to update the version of `SQLITE3.DLL` in your Python installation's top-level `DLLs` folder to one compiled from SQLite 3.8.3 or later. 63 64 {{{ 65 #!sql 66 WITH 67 cutoff_time(n, usec) AS 68 -- January 1 1970 was a Thursday. 69 -- We want to break at midnight between Sunday and Monday, 70 -- which is why there is a 3-day offset in the timestamps calculation. 71 (SELECT 0, 1e6 * (strftime('%s', 'now') - 72 ((strftime('%s', 'now') + (86400 * 3)) % (86400 * 7))) 73 UNION ALL 74 SELECT n + 1, usec - (1e6 * 86400 * 7) FROM cutoff_time LIMIT 520), 75 76 -- The following line stops Trac from limiting the output displayed 77 -- on a single page (leave it in as a comment to do this): 78 -- @LIMIT_OFFSET@ 79 -- I don't think it works with Trac prior to version 1.0 though. 80 81 open_time(id, usec) AS 82 (SELECT id, time FROM ticket), 83 84 close_time(id, usec) AS 85 (SELECT ticket, MAX(time) 86 FROM ticket_change 87 WHERE field = 'status' AND newvalue = 'closed' 88 GROUP BY ticket), 89 90 num_closed(n, cnt) AS 91 (SELECT n, COUNT(id) 92 FROM cutoff_time 93 JOIN close_time ON cutoff_time.usec > close_time.usec 94 GROUP BY n), 95 96 num_opened(n, cnt) AS 97 (SELECT n, COUNT(id) 98 FROM cutoff_time 99 JOIN open_time ON cutoff_time.usec > open_time.usec 100 GROUP BY n) 101 102 SELECT 103 date(cutoff_time.usec / 1e6, 'unixepoch') AS 'Date', 104 num_opened.cnt - IFNULL(num_closed.cnt, 0) AS 'Backlog', 105 num_opened.cnt AS 'Opened', 106 IFNULL(num_closed.cnt, 0) AS 'Closed' 107 FROM cutoff_time 108 JOIN num_opened USING (n) 109 LEFT OUTER JOIN num_closed USING (n) 110 ORDER BY date 111 }}} 112 113 === Report the number of open tickets with particular values of a custom field over time 114 115 Here's a more complex report that looks for tickets of type 'fault' that have a custom field 'safety_related'. At any time, this field can have one of three values that classify the safety status of the ticket: 'Not assessed', 'Safety related' or 'Not safety related'. The report plots the count of the number of open tickets with each safety status at the start of every week for the past three years. 63 116 64 117 {{{ … … 68 121 -- Includes a prediction of the status at the start of next week. 69 122 period(n) AS 70 -- January 1 1970 was a Thursday.71 -- To break periods at midnight between Sunday and Monday,72 -- a 3-day offset is placed in the timestamps calculation.73 123 (SELECT (strftime('%s', 'now') + (86400 * 7) - 74 124 ((strftime('%s', 'now') + (86400 * 3)) % (86400 * 7))) … … 76 126 SELECT n - (86400 * 7) FROM period LIMIT 156), 77 127 78 -- The following line stops Trac from limiting the output displayed79 -- on a single page (leave it in as a comment to do this):80 128 -- @LIMIT_OFFSET@ 81 -- I don't think it works with Trac prior to version 1.0 though.82 129 83 130 -- Restrict query to a particular subset of tickets here: