One of the things that bugs me with the default Trac install is the ticket list. By default the Active Tickets list sorts by value (?), milestone, type and time. Usually however I want to sort by Ticket # so I can see what I added most recently. Luckily Trac makes this easy as the Ticket ‘reports’ are driven by simply SQL queries.

Depending on your permissions when you click on an available report you should see several buttons at the top of your screen:

Simply click Edit Report and you will be taken to a dialog where you can adjust the title of the report, the description and the query which pulls the data for the report!

The query for the default Active Ticket report looks like:

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

Changing the sort order is a simple matter of adding an additional field to the ORDER BY:

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 ticket desc, p.value, milestone, t.type, time

Since it’s simple SQL it’s very easy to create new reports as well. I’ve created a few new ones including one for closed tickets and several to pull up tickets based on certain components, etc.