trac_ms_apps=> select * from report
trac_ms_apps-> ;

 id | author |                        title                         | sql | description
----+--------+------------------------------------------------------+-----+-------------
  1 |        | Active Tickets                                       |
SELECT p.value AS __color__,
   id AS ticket, summary, component, version, milestone, t.type AS type,
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p
  WHERE status IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY p.value, milestone, t.type, time
    |
 * List all active tickets by priority.
 * Color each row based on priority.
 * If a ticket has been accepted, a '*' is appended after the owner's name


  2 |        | Active Tickets by Version                            |
SELECT p.value AS __color__,
   version AS __group__,
   id AS ticket, summary, component, version, t.type AS type,
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p
  WHERE status IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY (version IS NULL),version, p.value, t.type, time
    |
This report shows how to color results by priority,
while grouping results by version.

Last modification time, description and reporter are included as hidden fields
for useful RSS export.


  3 |        | All Tickets by Milestone                             |
SELECT p.value AS __color__,
   milestone||' Release' AS __group__,
   id AS ticket, summary, component, version, t.type AS type,
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p
  WHERE status IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY (milestone IS NULL),milestone, p.value, t.type, time
    |
This report shows how to color results by priority,
while grouping results by milestone.

Last modification time, description and reporter are included as hidden fields
for useful RSS export.

  4 |        | Assigned, Active Tickets by Owner                    |

SELECT p.value AS __color__,
   owner AS __group__,
   id AS ticket, summary, component, milestone, t.type AS type, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t,enum p
  WHERE status = 'assigned'
AND p.name=t.priority AND p.type='priority'
  ORDER BY owner, p.value, t.type, time
    |
List assigned tickets, group by ticket owner, sorted by priority.


  5 |        | Assigned, Active Tickets by Owner (Full Description) |
SELECT p.value AS __color__,
   owner AS __group__,
   id AS ticket, summary, component, milestone, t.type AS type, time AS created,
   description AS _description_,
   changetime AS _changetime, reporter AS _reporter
  FROM ticket t, enum p
  WHERE status = 'assigned'
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY owner, p.value, t.type, time
    |
List tickets assigned, group by ticket owner.
This report demonstrates the use of full-row display.


  6 |        | All Tickets By Milestone  (Including closed)         |
SELECT p.value AS __color__,
   t.milestone AS __group__,
   (CASE status
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE
        (CASE owner WHEN '$USER' THEN 'font-weight: bold' END)
    END) AS __style__,
   id AS ticket, summary, component, status,
   resolution,version, t.type AS type, priority, owner,
   changetime AS modified,
   time AS _time,reporter AS _reporter
  FROM ticket t,enum p
  WHERE p.name=t.priority AND p.type='priority'
  ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'),
        (CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC
    |
A more complex example to show how to make advanced reports.

  7 |        | My Tickets                                           |
SELECT p.value AS __color__,
   (CASE status WHEN 'assigned' THEN 'Assigned' ELSE 'Owned' END) AS __group__,
   id AS ticket, summary, component, version, milestone,
   t.type AS type, priority, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p
  WHERE t.status IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.type = 'priority' AND owner = '$USER'
  ORDER BY (status = 'assigned') DESC, p.value, milestone, t.type, time
    |
This report demonstrates the use of the automatically set
$USER dynamic variable, replaced with the username of the
logged in user when executed.


  8 |        | Active Tickets, Mine first                           |
SELECT p.value AS __color__,
   (CASE owner
     WHEN '$USER' THEN 'My Tickets'
     ELSE 'Active Tickets'
    END) AS __group__,
   id AS ticket, summary, component, version, milestone, t.type AS type,
   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
   time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket t, enum p
  WHERE status IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY (owner = '$USER') DESC, p.value, milestone, t.type, time
    |
 * List all active tickets by priority.
 * Show all tickets owned by the logged in user in a group first.