[[PageOutline(2-5,Contents,pullout)]] = Create graphical reports using the Dygraphs charting library == Description This plugin is based on Rob Guttman's [[VisualizationPlugin|Visualization Plugin]], except that it uses the MIT-licensed [http://dygraphs.com Dygraphs] charting library, instead of the [https://developers.google.com/chart/?csw=1&safe=on Google Visualization API]. Dygraphs was primarily designed to handle date and timeseries and hence this plugin is suited to display ticket statistics. Dygraphs charts can be legally used offline, which the Google APIs don't allow. There are corporate Trac installations that for various reasons can't be connected to the Internet. Tested with Trac 0.12 and 1.0. == !Bugs/Feature Requests Existing bugs and feature requests for DygraphsVisualizationPlugin are [report:9?COMPONENT=DygraphsVisualizationPlugin here]. If you have any issues, create a [/newticket?component=DygraphsVisualizationPlugin new ticket]. [[TicketQuery(component=DygraphsVisualizationPlugin&group=type,format=progress)]] == Download Download the zipped source from [export:dygraphsvisualizationplugin here]. == Source You can check out DygraphsVisualizationPlugin from [/svn/dygraphsvisualizationplugin here] using Subversion, or [source:dygraphsvisualizationplugin browse the source] with Trac. == Installation General instructions on installing Trac plugins can be found on the [TracPlugins#InstallingaTracplugin TracPlugins] page. Once installed, enable graphical output for a particular report in the `trac.ini` file: {{{#!ini [dyviz] reports = 10 options = width:1000,height:400 }}} Customise options for a particular report: {{{#!ini [dyviz.report/10] options = plotter:barChartPlotter,labels:labels,colors:['red','orange','green'],includeZero:true,animatedZooms:true,legend:'follow' }}} The options are generally passed directly to the Dygraphs library. To allow the `labelsDiv` option to be used effectively, the plugin inserts empty divs "dyviz_above" and "dyviz_below" above and below the chart, respectively. == Example graphs === Report the history of tickets open and closed It is often useful to be able to generate reports that span a period of time, for monitoring the progress of a project. 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. 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: {{{#!sql WITH cutoff_time(n, usec) AS -- January 1 1970 was a Thursday. -- We want to break at midnight between Sunday and Monday, -- which is why there is a 3-day offset in the timestamps calculation. (SELECT 0, 1e6 * (strftime('%s', 'now') - ((strftime('%s', 'now') + (86400 * 3)) % (86400 * 7))) UNION ALL SELECT n + 1, usec - (1e6 * 86400 * 7) FROM cutoff_time LIMIT 520), -- The following line stops Trac from limiting the output displayed -- on a single page (leave it in as a comment to do this): -- @LIMIT_OFFSET@ -- I don't think it works with Trac prior to version 1.0 though. open_time(id, usec) AS (SELECT id, time FROM ticket), close_time(id, usec) AS (SELECT ticket, MAX(time) FROM ticket_change WHERE field = 'status' AND newvalue = 'closed' GROUP BY ticket), num_closed(n, cnt) AS (SELECT n, COUNT(id) FROM cutoff_time JOIN close_time ON cutoff_time.usec > close_time.usec GROUP BY n), num_opened(n, cnt) AS (SELECT n, COUNT(id) FROM cutoff_time JOIN open_time ON cutoff_time.usec > open_time.usec GROUP BY n) SELECT date(cutoff_time.usec / 1e6, 'unixepoch') AS 'Date', num_opened.cnt - IFNULL(num_closed.cnt, 0) AS 'Backlog', num_opened.cnt AS 'Opened', IFNULL(num_closed.cnt, 0) AS 'Closed' FROM cutoff_time JOIN num_opened USING (n) LEFT OUTER JOIN num_closed USING (n) ORDER BY date }}} === Report the number of open tickets with particular values of a custom field over time Here is 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: {{{#!sql WITH -- Select the time period of interest here, e.g. last three years. -- Includes a prediction of the status at the start of next week. period(n) AS (SELECT (strftime('%s', 'now') + (86400 * 7) - ((strftime('%s', 'now') + (86400 * 3)) % (86400 * 7))) UNION ALL SELECT n - (86400 * 7) FROM period LIMIT 156), -- @LIMIT_OFFSET@ -- Restrict query to a particular subset of tickets here: ticket_subset(id, time) AS (SELECT id, time / 1e6 FROM ticket WHERE type LIKE 'fault'), -- Select the custom field of interest here (in two places): custom_subset AS (SELECT ticket, value FROM ticket_custom WHERE name = 'safety_related'), change_subset AS (SELECT ticket, time, oldvalue, newvalue FROM ticket_change WHERE field = 'safety_related'), close_time(id, close) AS -- Open tickets are assigned a 'fake' close time of 1 week from now. (SELECT id, IFNULL(MAX(tch.time / 1e6), strftime('%s', 'now') + 86400 * 7) FROM ticket_subset LEFT JOIN ticket_change tch ON id = ticket AND field = 'status' AND newvalue = 'closed' GROUP BY id), initial_value(id, init, ctime) AS (SELECT t.id, IFNULL(tch.oldvalue, CASE WHEN tch.newvalue IS NULL THEN cu.value ELSE NULL END), MIN(tch.time) FROM ticket_subset t JOIN custom_subset cu ON t.id = cu.ticket LEFT JOIN change_subset tch ON t.id = tch.ticket GROUP BY t.id), current_value(n, id, value) AS (SELECT n, t.id, IFNULL(ch.newvalue, i.init) FROM period JOIN close_time c, ticket_subset t ON t.id = c.id AND t.time <= n AND c.close > n LEFT JOIN change_subset ch ON t.id = ch.ticket AND ch.time = (SELECT MAX(ch2.time) FROM change_subset ch2 WHERE t.id = ch2.ticket AND ch2.time / 1e6 <= n AND close > n) JOIN initial_value i ON t.id = i.id AND t.time <= n AND close > n GROUP BY n, t.id) -- Edit here according to the actual field values: SELECT date(p.n, 'unixepoch') AS 'Date', SUM(CASE WHEN value = 'Safety related' THEN 1 ELSE 0 END) AS 'Safety related', SUM(CASE WHEN value = 'Not assessed' THEN 1 ELSE 0 END) AS 'Not assessed', SUM(CASE WHEN value = 'Not safety related' THEN 1 ELSE 0 END) AS 'Not safety related' FROM period p LEFT JOIN current_value USING (n) GROUP BY p.n }}} == Recent Changes [[ChangeLog(dygraphsvisualizationplugin, 3)]] == !Author/Contributors **Author:** [wiki:ash] [[BR]] **Maintainer:** [[Maintainer]] [[BR]] **Contributors:**