Custom Reports Configuration

TracReports#CreatingCustomReports describes how to create custom reports.

This page contains SQL for reports that may be of interest to many users.

Available Examples

1. Ticket reports
   1. User Report
   2. Popular tickets - to do
   3. Popular tickets - done
   4. Popular Voted Tickets
   5. Overview for next Milestones
2. Non Ticket Reports
   1. User Changesets
   2. Wiki Changes

Ticket reports

User Report

When one visits a Trac system as a user of a product and not as a developer of the product, one often wants to search for those tickets of personal interest. Such tickets could be defined as those tickets where the user is the Reporter, in the CC or additionally as a commenter:

SELECT 
  DISTINCT 
   p.value AS __color__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
    END) AS __style__,
   id AS ticket, summary, component, milestone, status, resolution, 
   t.time AS created, changetime AS modified,
   priority AS _priority, reporter AS _reporter, cc AS _cc
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON id = tc.ticket
  WHERE reporter = '$USER' 
   OR cc LIKE '$USER,%'
   OR cc LIKE '% $USER'
   OR cc LIKE '% $USER,%'
   OR cc = '$USER'
   OR (tc.author = '$USER' AND tc.field = 'comment')
  ORDER BY
    (status = 'closed'), 
    (CASE status
       WHEN 'closed' THEN changetime
       ELSE (-1) * CAST(p.value AS int)
    END) DESC

To include tickets that the user made any changes to, whether a comment was included or not, change the following:

Report.sql

@@ -15 +15 @@
    OR cc LIKE '% $USER'
    OR cc LIKE '% $USER,%'
    OR cc = '$USER'
-   OR (tc.author = '$USER' AND tc.field = 'comment')
+   OR tc.author = '$USER'
   ORDER BY
     (status = 'closed'),
     (CASE status

To exclude tickets that the user made comments to and thus just those they are the reporter or in the CC, delete the following:

Report.sql

@@ -9 +9 @@
    priority AS _priority, reporter AS _reporter, cc AS _cc
   FROM ticket t
   LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
-  LEFT JOIN ticket_change tc ON id = tc.ticket
   WHERE reporter = '$USER'
    OR cc LIKE '$USER,%'
    OR cc LIKE '% $USER'
    OR cc LIKE '% $USER,%'
    OR cc = '$USER'
-   OR (tc.author = '$USER' AND tc.field = 'comment')
   ORDER BY
     (status = 'closed'),
     (CASE status

Popular tickets - to do

This lists tickets that are on the TODO list for a long time.

The criteria for inclusion is that the feature request or bug report is that at least 10 different people participated to the ticket activity.

Tickets are ordered by popularity, defined as the amount of activity on a ticket over time, using a formula taking into account the number of participants in the discussion and the number of people on CC:.

This is report {32} on this site.

SELECT p.value AS __color__,
  (SELECT
    ROUND(( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(t.cc) * 1.5) 
             / (delta/86400.0)
          ) * 10.0
         ) / 10.0
   FROM (SELECT ((time.now * 1000000 - changetime) / 1000000.0) AS delta
        ) AS ct
  ) AS Popularity,
  t.type,
  id AS ticket, summary,
  component, 
  version,
  time AS created
FROM ticket t LEFT JOIN
   (SELECT ticket, COUNT(DISTINCT author) AS author_cnt, COUNT(*) AS change_cnt
    FROM ticket_change
    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket),
   enum p,
   (SELECT EXTRACT(epoch FROM current_timestamp) AS now) time
WHERE t.status != 'closed'
  -- AND time.now - t.time > 86400.0 * 30 * 2
  -- well, the above was not needed as they are all older than 1 year
  AND tc.author_cnt >= 10
  AND p.name = t.priority AND p.type = 'priority'
ORDER BY
 ( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(t.cc) * 1.5) 
    / ((time.now * 1000000 - changetime) / 1000000.0) ) DESC,  
 p.value, t.severity, time

Popular tickets - done

In order to balance report {32} (tickets that are on the TODO list for a long time), we can also show that we managed to implement popular feature requests over time.

The criteria for inclusion for the feature request or bug report is that at least 10 different people participated to the ticket activity.

Tickets are ordered by popularity, defined as the amount of activity on a ticket over time.

This is report {33} on this site.

SELECT p.value AS __color__,
  (SELECT
    ROUND(( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(COALESCE(t.cc, '')) * 1.5) 
             / (delta/86400.0)
          ) * 10.0
         ) / 10.0
   FROM (SELECT ((time.now * 1000000 - changetime) / 1000000.0) AS delta
        ) AS ct
  ) AS Popularity,
  t.type,
  id AS ticket, summary, component, 
  milestone, version,
  time AS created
FROM ticket t LEFT JOIN
   (SELECT ticket, COUNT(DISTINCT author) AS author_cnt, COUNT(*) AS change_cnt
    FROM ticket_change
    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket),
   enum p,
   (SELECT EXTRACT(epoch FROM current_timestamp) AS now) time
WHERE t.status = 'closed' AND t.resolution = 'fixed'
  -- AND time.now - t.time > 86400.0 * 30 * 2
  -- well, the above was not needed as they are all older than 1 year
  AND tc.author_cnt >= 10
  AND p.name = t.priority AND p.type = 'priority'
ORDER BY
 ( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(COALESCE(t.cc, '')) * 1.5) 
    / ((time.now * 1000000 - changetime) / 1000000.0) ) DESC,  
 p.value, t.severity, time

Popular Voted Tickets

Tickets ordered by number of votes, where there are at least 2 votes.

This is report {34} on this site.

SELECT p.value AS __color__,
   t.type AS type, id AS ticket, SUM(v.vote) AS votes, summary, component, version, milestone,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p, votes v
  WHERE status <> 'closed'
AND p.name = t.priority AND p.type = 'priority'
AND v.resource = 'ticket/' || id
GROUP BY id, summary, component, version, milestone, t.type, owner, time,
  changetime, description, reporter, p.value, status
HAVING SUM(v.vote) > 1
ORDER BY votes DESC, milestone, t.type, time

Overview for next Milestones

Show count of opened tickets by type for the next Milestones.

This is report {37} on this site.

SELECT 
 name AS id, 'milestone' AS _realm,
 (SELECT count(*) FROM ticket 
  WHERE milestone = m.name AND type = 'enhancement' AND status != 'closed') as Enhancements,
 (SELECT count(*) FROM ticket 
  WHERE milestone = m.name AND type = 'defect' AND status != 'closed') as Defects,
 (SELECT count(*) FROM ticket 
  WHERE milestone = m.name AND type = 'task' AND status != 'closed') as Tasks
FROM milestone AS m
WHERE completed = 0
ORDER BY name

Non Ticket Reports

SQL reports do not have to be limited to tickets. They can report on revisions, wiki pages, or user activity.

User Changesets

The follow report will lists all of the changes for the current user, or add "?USER = user" to the end of the URL to report on another user:

SELECT 
   'changeset' AS _realm, rev AS id, 
   time AS date, time, 
   SUBSTR(message, 1, 80) AS message
FROM revision
WHERE author='$USER'
ORDER BY time DESC

This report only gives the first 80 characters of the check-in message. To give the full message, make the following change:

Report.sql

@@ -1 +1 @@
  SELECT 
     'changeset' AS _realm, rev as id, 
     time AS date, time, 
-    SUBSTR(message, 1, 80) AS message
+    message AS _message_
  FROM revision
  WHERE author='$USER'
  ORDER BY time DESC

Wiki Changes

This reports the wiki pages ever modified by the current user, or add "?USER = user" to the end of the URL to report on another user. Version is the highest version that the user modified:

SELECT 
   'wiki' AS _realm, 
   name AS id, 
   MAX(version) AS Version,
   time AS date, time, 
   comment
FROM wiki
WHERE author = '$USER'
GROUP BY name

For wiki pages that were last modified by the current user:

SELECT 
   'wiki' AS _realm, 
   wiki.name AS id, 
   time AS date, time, 
   comment
FROM wiki,
 (SELECT MAX(version) AS maxVer, name
  FROM wiki
  GROUP BY name) maxResults
WHERE wiki.name = maxResults.name
 AND version = maxVer
 AND author = '$USER'
ORDER BY wiki.name