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.

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.

Query for Report:

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