Edgewall Software

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


Ignore:
Timestamp:
Aug 4, 2015, 6:50:11 PM (4 years ago)
Author:
figaro
Comment:

Cosmetic changes

Legend:

Unmodified
Added
Removed
Modified
  • CookBook/Configuration/Reports

    v7 v8  
    1 = Custom Reports Configuration =
     1= Custom Reports Configuration
    22
    33TracReports#CreatingCustomReports describes how to create custom reports.
    44
    55This page contains SQL for reports that may be of interest to many users.
     6
    67[[PageOutline(2-3,Available Examples,inline)]]
    78
    8 == Ticket reports ==
    9 
    10 === User Report ===
    11 
    12 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.
    13 
    14 Query for Report:
    15 {{{
    16 #!sql
     9== Ticket reports
     10
     11=== User Report
     12
     13When 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:
     14
     15{{{#!sql
    1716SELECT
    1817  DISTINCT
     
    3231   OR cc LIKE '% $USER,%'
    3332   OR cc = '$USER'
    34    OR (tc.author='$USER' AND tc.field = 'comment')
     33   OR (tc.author = '$USER' AND tc.field = 'comment')
    3534  ORDER BY
    3635    (status = 'closed'),
     
    4241
    4342To include tickets that the user made any changes to, whether a comment was included or not, change the following:
    44 {{{
    45 #!diff
     43{{{#!diff
    4644--- Report.sql
    4745+++ Report.sql
     
    5048    OR cc LIKE '% $USER,%'
    5149    OR cc = '$USER'
    52 -   OR (tc.author='$USER' AND tc.field = 'comment')
    53 +   OR tc.author='$USER'
     50-   OR (tc.author = '$USER' AND tc.field = 'comment')
     51+   OR tc.author = '$USER'
    5452   ORDER BY
    5553     (status = 'closed'),
    5654     (CASE status
    5755}}}
     56
    5857To exclude tickets that the user made comments to and thus just those they are the reporter or in the CC, delete the following:
    59 {{{
    60 #!diff
     58{{{#!diff
    6159--- Report.sql
    6260+++ Report.sql
     
    7169    OR cc LIKE '% $USER,%'
    7270    OR cc = '$USER'
    73 -   OR (tc.author='$USER' AND tc.field = 'comment')
     71-   OR (tc.author = '$USER' AND tc.field = 'comment')
    7472   ORDER BY
    7573     (status = 'closed'),
     
    7876
    7977
    80 === Popular tickets - to do ===
     78=== Popular tickets - to do
    8179
    8280This lists tickets that are on the TODO list for a long time.
     
    8482The criteria for inclusion is that the feature request or bug report is that '''at least 10 different people participated to the ticket activity'''.
    8583
    86 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:, ...)
     84Tickets 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:.
    8785
    8886This is report {32} on this site.
     
    10199  component,
    102100  version,
    103   time as created
     101  time AS created
    104102FROM ticket t LEFT JOIN
    105    (SELECT ticket, count(distinct author) as author_cnt,
    106           count(*) as change_cnt
     103   (SELECT ticket, COUNT(DISTINCT author) AS author_cnt, COUNT(*) AS change_cnt
    107104    FROM ticket_change
    108105    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket),
    109106   enum p,
    110    (SELECT extract(epoch from current_timestamp) as now) time
     107   (SELECT EXTRACT(epoch FROM current_timestamp) AS now) time
    111108WHERE t.status != 'closed'
    112109  -- AND time.now - t.time > 86400.0 * 30 * 2
    113   -- well, the above was not really needed as they are all older than 1 year...
     110  -- well, the above was not needed as they are all older than 1 year
    114111  AND tc.author_cnt >= 10
    115112  AND p.name = t.priority AND p.type = 'priority'
    116113ORDER BY
    117114 ( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(t.cc) * 1.5)
    118     / ((time.now * 1000000 - changetime) / 1000000.0) ) desc
     115    / ((time.now * 1000000 - changetime) / 1000000.0) ) DESC
    119116 p.value, t.severity, time
    120117
    121118}}}
    122119
    123 
    124 === Popular tickets - done ===
    125 
    126 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 ;-)
     120=== Popular tickets - done
     121
     122In 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.
    127123
    128124The criteria for inclusion for the feature request or bug report is that '''at least 10 different people participated to the ticket activity'''.
     
    141137        ) AS ct
    142138  ) AS Popularity,
    143  --
    144  --  ROUND(((tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(COALESCE(t.cc, '')) * 1.5)
    145  -- / ((time.now - t.time)/(86400.0 * 30))) * 10.0) / 10.0 AS Popularity,
    146  --
    147139  t.type,
    148140  id AS ticket, summary, component,
    149141  milestone, version,
    150   time as created
     142  time AS created
    151143FROM ticket t LEFT JOIN
    152    (SELECT ticket, count(distinct author) as author_cnt,
    153           count(*) as change_cnt
     144   (SELECT ticket, COUNT(DISTINCT author) AS author_cnt, COUNT(*) AS change_cnt
    154145    FROM ticket_change
    155146    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket),
    156147   enum p,
    157    (SELECT extract(epoch from current_timestamp) as now) time
     148   (SELECT EXTRACT(epoch FROM current_timestamp) AS now) time
    158149WHERE t.status = 'closed' AND t.resolution = 'fixed'
    159150  -- AND time.now - t.time > 86400.0 * 30 * 2
    160   -- well, the above was not really needed as they are all older than 1 year...
     151  -- well, the above was not needed as they are all older than 1 year
    161152  AND tc.author_cnt >= 10
    162153  AND p.name = t.priority AND p.type = 'priority'
    163154ORDER BY
    164155 ( (tc.author_cnt + tc.change_cnt * 0.5 + LENGTH(COALESCE(t.cc, '')) * 1.5)
    165     / ((time.now * 1000000 - changetime) / 1000000.0) ) desc
     156    / ((time.now * 1000000 - changetime) / 1000000.0) ) DESC
    166157 p.value, t.severity, time
    167158}}}
    168159
    169160
    170 === Popular Voted Tickets ===
    171 
    172 Tickets ordered by number of votes (> 1).
     161=== Popular Voted Tickets
     162
     163Tickets ordered by number of votes, where there are at least 2 votes.
    173164
    174165This is report {34} on this site.
    175166{{{#!sql
    176167SELECT p.value AS __color__,
    177    t.type AS type, id AS ticket, sum(v.vote) as votes, summary, component, version,
    178 milestone,
     168   t.type AS type, id AS ticket, SUM(v.vote) AS votes, summary, component, version, milestone,
    179169   time AS created,
    180170   changetime AS _changetime, description AS _description,
     
    184174AND p.name = t.priority AND p.type = 'priority'
    185175AND v.resource = 'ticket/' || id
    186 GROUP BY id, summary, component, version, milestone, t.type, owner,
    187 time,
     176GROUP BY id, summary, component, version, milestone, t.type, owner, time,
    188177  changetime, description, reporter, p.value, status
    189 HAVING sum(v.vote) > 1
    190  ORDER BY votes DESC, milestone, t.type, time
    191 }}}
    192 
     178HAVING SUM(v.vote) > 1
     179ORDER BY votes DESC, milestone, t.type, time
     180}}}
    193181
    194182=== Overview for next Milestones ===
     
    201189 name AS id, 'milestone' AS _realm,
    202190 (SELECT count(*) FROM ticket
    203   WHERE milestone=m.name AND type='enhancement' AND status != 'closed') as Enhancements,
     191  WHERE milestone = m.name AND type = 'enhancement' AND status != 'closed') as Enhancements,
    204192 (SELECT count(*) FROM ticket
    205   WHERE milestone=m.name AND type='defect' AND status != 'closed') as Defects,
     193  WHERE milestone = m.name AND type = 'defect' AND status != 'closed') as Defects,
    206194 (SELECT count(*) FROM ticket
    207   WHERE milestone=m.name AND type='task' AND status != 'closed') as Tasks
     195  WHERE milestone = m.name AND type = 'task' AND status != 'closed') as Tasks
    208196FROM milestone AS m
    209197WHERE completed = 0
     
    211199}}}
    212200
    213 
    214 == Non Ticket Reports ==
    215 
    216 SQL reports do not have to be limited to tickets.  They can report on revisions, wiki pages, or user activity.
    217 
    218 === User Changesets ===
    219 
    220 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.
    221 {{{
    222 #!sql
    223 SELECT
    224    'changeset' AS _realm, rev as id,
     201== Non Ticket Reports
     202
     203SQL reports do not have to be limited to tickets. They can report on revisions, wiki pages, or user activity.
     204
     205=== User Changesets
     206
     207The 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:
     208{{{#!sql
     209SELECT
     210   'changeset' AS _realm, rev AS id,
    225211   time AS date, time,
    226    SUBSTR(message, 1, 80 )AS message
     212   SUBSTR(message, 1, 80) AS message
    227213FROM revision
    228214WHERE author='$USER'
    229215ORDER BY time DESC
    230216}}}
    231 This report only gives the first 80 characters of the check-in message.  To give the full message, make the following change:
    232 {{{
    233 #!diff
     217
     218This report only gives the first 80 characters of the check-in message. To give the full message, make the following change:
     219{{{#!diff
    234220--- Report.sql
    235221+++ Report.sql
     
    238224     'changeset' AS _realm, rev as id,
    239225     time AS date, time,
    240 -    SUBSTR(message, 1, 80 )AS message
     226-    SUBSTR(message, 1, 80) AS message
    241227+    message AS _message_
    242228  FROM revision
     
    245231}}}
    246232
    247 
    248 === Wiki Changes ===
    249 
    250 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.
    251 {{{
    252 #!sql
     233=== Wiki Changes
     234
     235This 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:
     236{{{#!sql
    253237SELECT
    254238   'wiki' AS _realm,
    255239   name AS id,
    256    max(version) AS Version,
     240   MAX(version) AS Version,
    257241   time AS date, time,
    258242   comment
    259243FROM wiki
    260 WHERE author='$USER'
     244WHERE author = '$USER'
    261245GROUP BY name
    262246}}}
     247
    263248For wiki pages that were last modified by the current user:
    264 {{{
    265 #!sql
     249{{{#!sql
    266250SELECT
    267251   'wiki' AS _realm,
     
    270254   comment
    271255FROM wiki,
    272  (SELECT max(version) AS maxVer, name
     256 (SELECT MAX(version) AS maxVer, name
    273257  FROM wiki
    274258  GROUP BY name) maxResults