= 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:
{{{
#!sql
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:
{{{
#!diff
--- Report.sql
+++ Report.sql
@@ -15,7 +15,7 @@
    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:
{{{
#!diff
--- Report.sql
+++ Report.sql
@@ -9,13 +9,11 @@
    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
}}}

== 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.
{{{
!#sql
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:
{{{
#!diff
--- Report.sql
+++ Report.sql
@@ -1,7 +1,7 @@
  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.
{{{
#!sql
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:
{{{
#!sql
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
}}}