= 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.
[[PageOutline(2-3,Available Examples,inline)]]

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

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


=== 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 secret formula taking into account the number of participants in the discussion, the number of people on CC:, ...)

This is report {32} on this site.
{{{#!sql
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 really 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.
{{{#!sql
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,
 -- 
 --  ROUND(((tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(COALESCE(t.cc, '')) * 1.5) 
 -- / ((time.now - t.time)/(86400.0 * 30))) * 10.0) / 10.0 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 really 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 (> 1).

This is report {34} on this site.
{{{#!sql
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.
{{{#!sql
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.
{{{
#!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
}}}