Version 4 (modified by 14 years ago) ( diff ) | ,
---|
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' 16 16 OR cc LIKE '% $USER,%' 17 17 OR cc = '$USER' 18 OR (tc.author='$USER' AND tc.field = 'comment')18 OR tc.author='$USER' 19 19 ORDER BY 20 20 (status = 'closed'), 21 21 (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 10 10 FROM ticket t 11 11 LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' 12 LEFT JOIN ticket_change tc ON id = tc.ticket13 12 WHERE reporter = '$USER' 14 13 OR cc LIKE '$USER,%' 15 14 OR cc LIKE '% $USER' 16 15 OR cc LIKE '% $USER,%' 17 16 OR cc = '$USER' 18 OR (tc.author='$USER' AND tc.field = 'comment')19 17 ORDER BY 20 18 (status = 'closed'), 21 19 (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.
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 2 2 "changeset" AS _realm, rev as id, 3 3 time AS date, time, 4 SUBSTR(message, 1, 80 )AS message4 message AS _message_ 5 5 FROM revision 6 6 WHERE author='$USER' 7 7 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