Changes between Version 4 and Version 5 of CookBook/Configuration/Reports

Timestamp:

Apr 22, 2010, 9:48:23 AM (14 years ago)

Author:

Remy Blank

Comment:

Added "interesting" reports from t.e.o.

CookBook/Configuration/Reports

@@ -5 +5 @@
 This page contains SQL for reports that may be of interest to many users.
 
-== User Report ==
+== 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.
@@ -74 +76 @@
 }}}
 
+
+=== 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 ==
 
@@ -79 +216 @@
 
 === 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.
 {{{
@@ -105 +243 @@
   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.
 {{{