Create graphical reports using the MIT-licensed Dygraphs charting library

Description

This plugin is based on Rob Guttman's Visualization Plugin, except that it uses the MIT-licensed ​Dygraphs charting library instead of the ​Google Visualization API.

This means that it 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.

Only tested with Trac 0.12 and 1.0 so far.

Bugs/Feature Requests

Existing bugs and feature requests for DygraphsVisualizationPlugin are here.

If you have any issues, create a new ticket.

task	1 / 1

Download

Download the zipped source from here.

Source

You can check out DygraphsVisualizationPlugin from ​here using Subversion, or browse the source​ with Trac.

Installation

General instructions on installing Trac plugins can be found on the TracPlugins page.

Once installed, enable graphical output for a particular report in the trac.ini file:

[dyviz]
reports = 10
options = width:1000,height:400

Customise options for a particular report:

[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 number of open tickets with particular values of a custom field over time

It's 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 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.

Note that this query uses Common Table Expressions. These have only been present in SQLite since ​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.

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
    -- January 1 1970 was a Thursday.
    -- To break periods at midnight between Sunday and Monday,
    -- a 3-day offset is placed in the timestamps calculation.
    (SELECT (strftime('%s', 'now') + (86400 * 7) -
               ((strftime('%s', 'now') + (86400 * 3)) % (86400 * 7)))
     UNION ALL
     SELECT n - (86400 * 7) FROM period LIMIT 156),

  -- 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.

  -- 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

14726 by ash on 2015-06-22 06:22:16

Add the 'multi column bar plotter' (taken from the example plotter at ​http://dygraphs.com/tests/plotters.html)

14725 by ash on 2015-06-22 06:14:50

Restructure into 'trunk' and 'tags', so that releases can be baselined

14724 by rjollos on 2015-06-21 19:19:18

Modified url for plugin name change. Refs #12411.

(more)

Author/Contributors

Author: ash
Maintainer: Jon Ashley
Contributors: