Changes between Version 7 and Version 8 of CookBook/Configuration/Reports

Timestamp:

Aug 4, 2015, 6:50:11 PM (9 years ago)

Author:

figaro

Comment:

Cosmetic changes

CookBook/Configuration/Reports

@@ -1 @@
-= Custom Reports Configuration =
+= 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
+== 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:
+
+{{{#!sql
 SELECT 
   DISTINCT 
@@ -32 +31 @@
    OR cc LIKE '% $USER,%'
    OR cc = '$USER'
-   OR (tc.author='$USER' AND tc.field = 'comment')
+   OR (tc.author = '$USER' AND tc.field = 'comment')
   ORDER BY
     (status = 'closed'), 
@@ -42 +41 @@
 
 To include tickets that the user made any changes to, whether a comment was included or not, change the following:
-{{{
-#!diff
+{{{#!diff
 --- Report.sql
 +++ Report.sql
@@ -50 +48 @@
     OR cc LIKE '% $USER,%'
     OR cc = '$USER'
--   OR (tc.author='$USER' AND tc.field = 'comment')
-+   OR tc.author='$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
+{{{#!diff
 --- Report.sql
 +++ Report.sql
@@ -71 +69 @@
     OR cc LIKE '% $USER,%'
     OR cc = '$USER'
--   OR (tc.author='$USER' AND tc.field = 'comment')
+-   OR (tc.author = '$USER' AND tc.field = 'comment')
    ORDER BY
      (status = 'closed'),
@@ -78 +76 @@
 
 
-=== Popular tickets - to do ===
+=== Popular tickets - to do
 
 This lists tickets that are on the TODO list for a long time.
@@ -84 +82 @@
 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:, ...)
+Tickets are ordered by ''popularity'', defined as the amount of activity on a ticket over time, using a formula taking into account the number of participants in the discussion and the number of people on CC:.
 
 This is report {32} on this site.
@@ -101 +99 @@
   component, 
   version,
-  time as created
+  time AS created
 FROM ticket t LEFT JOIN
-   (SELECT ticket, count(distinct author) as author_cnt,
-          count(*) as change_cnt
+   (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
+   (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...
+  -- well, the above was not 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,  
+    / ((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 ;-)
+=== 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'''.
@@ -141 +137 @@
         ) 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
+  time AS created
 FROM ticket t LEFT JOIN
-   (SELECT ticket, count(distinct author) as author_cnt,
-          count(*) as change_cnt
+   (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
+   (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...
+  -- well, the above was not 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,  
+    / ((time.now * 1000000 - changetime) / 1000000.0) ) DESC,  
  p.value, t.severity, time
 }}}
 
 
-=== Popular Voted Tickets ===
-
-Tickets ordered by number of votes (> 1).
+=== Popular Voted Tickets
+
+Tickets ordered by number of votes, where there are at least 2 votes.
 
 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,
+   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,
@@ -184 +174 @@
 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,
+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
-}}}
-
+HAVING SUM(v.vote) > 1
+ORDER BY votes DESC, milestone, t.type, time
+}}}
 
 === Overview for next Milestones ===
@@ -201 +189 @@
  name AS id, 'milestone' AS _realm,
  (SELECT count(*) FROM ticket 
-  WHERE milestone=m.name AND type='enhancement' AND status != 'closed') as Enhancements,
+  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,
+  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
+  WHERE milestone = m.name AND type = 'task' AND status != 'closed') as Tasks
 FROM milestone AS m
 WHERE completed = 0
@@ -211 +199 @@
 }}}
 
-
-== 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, 
+== 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
+   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
+
+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
@@ -238 +224 @@
      'changeset' AS _realm, rev as id, 
      time AS date, time, 
--    SUBSTR(message, 1, 80 )AS message
+-    SUBSTR(message, 1, 80) AS message
 +    message AS _message_
   FROM revision
@@ -245 +231 @@
 }}}
 
-
-=== 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
+=== 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,
+   MAX(version) AS Version,
    time AS date, time, 
    comment
 FROM wiki
-WHERE author='$USER'
+WHERE author = '$USER'
 GROUP BY name
 }}}
+
 For wiki pages that were last modified by the current user:
-{{{
-#!sql
+{{{#!sql
 SELECT 
    'wiki' AS _realm, 
@@ -270 +254 @@
    comment
 FROM wiki,
- (SELECT max(version) AS maxVer, name
+ (SELECT MAX(version) AS maxVer, name
   FROM wiki
   GROUP BY name) maxResults