5 Really Useful Trac Reports

For those of you who are not familiar with the project management software called The Trac Project please proceed to the Trac Guide. I’ve been working with this project management tool for quite some time now and the latest upgrades are just awesome. I believe the solution for private projects’ RSS feeds has been found a few months ago somewhere here. The iCalendar issue remains, but it’s probably solved the exact same way – http authentication, although not all iCalendar clients and RSS aggregators support that yet. My favourite Feedly doesn’t.

Before showing off the snipets that I wrote for Trac Reports I’d like to give you one little hint if you’re running Trac on an old (but stable) OS version, such as Fedora Core 8. Amazon EC2 still ships the Fedora 8 operating system as default for Linux-based EC2 instances, and a simple yum install trac will get you version 0.10.x, which is outdated. The trick here is to install:

yum install python-setuptools
yum install python-setuptools-devel

Which include a package called easy_install. Since you’ve got an old version of Trac installed, use easy_install to update it, like this:

easy_install -U trac

Bam! Wasn’t that easy? One more situation where you can use easy_install is to install Trac plugins directly from their repositories (instead of the old way by uploading source files).

Okay, now back to the reports. I made these with a little experimenting with the Trac database. It’s structure is pretty much transparent and obvious, so if you’re an SQL genius then go ahead and write your own. Here’s my most useful list …

1. All Tickets Reported by Me

This report is very useful to track the status of all your tickets. Shows all types of tickets in all states (opened, closed). Have you ever submitted a ticket and then couldn’t find it in your submitted tickets because it was marked as fixed or wontfix? Here’s the SQL query:

SELECT p.value AS __color__,
    (CASE status WHEN 'accepted' THEN 'Accepted'
        WHEN 'new' THEN 'New'
        WHEN 'owned' THEN 'Owned'
        WHEN 'closed' THEN 'Closed'
        WHEN 'assigned' THEN 'Assigned'
    ELSE 'Unknown' END) AS __group__,

    id AS ticket, summary, component, milestone,
    t.type AS type, priority, time AS created,
    changetime as _changetime, description AS _description, owner

    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE reporter = $USER
    ORDER BY (status = 'accepted') DESC, (status = 'assigned') DESC,
        (status = 'new') DESC, CAST(p.value AS int), milestone, t.type, time

2. All Tickets Owned by Me

This report is useful to track your own status. See your active tickets, new ones and once that have been closed.

SELECT p.value AS __color__,
    (CASE status WHEN 'accepted' THEN 'Accepted'
    WHEN 'closed' THEN 'Closed' 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
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE owner = $USER
    ORDER BY (status = 'accepted') DESC, (status = 'assigned') DESC,
        (status = 'closed') DESC, CAST(p.value AS int), milestone, t.type, time

3. Assigned and Accepted Tickets by Owner

Such reports are very useful to track the workflow of all the Trac contributors on one page.

SELECT p.value AS __color__,
    (CASE status WHEN 'accepted' THEN 'Accepted by: '
        WHEN 'assigned' THEN 'Assigned to: ' END) ||
    owner AS __group__,

    id AS ticket, summary, component, milestone, t.type AS type, time AS created,
    changetime AS _changetime, description AS _description, priority, reporter

    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status = 'assigned' OR status = 'accepted'
    ORDER BY owner, CAST(p.value AS int), t.type, time

4. Active Tickets by Milestone

Track the overall progress of the development team, see what’s left to do until a certain milestone is reached.

SELECT p.value AS __color__,
    'Milestone '||milestone AS __group__,
    id AS ticket, summary, component, version, t.type AS type,
    owner, status, time AS created, changetime AS _changetime,
    description AS _description, reporter AS _reporter

    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status <> 'closed'
    ORDER BY (milestone IS NULL),
        milestone, CAST(p.value AS int), t.type, time

5. Closed Tickets by Owner

Finally, this report is extremely useful to track progress by owner. Shows clearly what the contributors have been up to lately.

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,
    priority, reporter

    FROM ticket t
    LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    WHERE status = 'closed'
    ORDER BY owner, CAST(p.value AS int), t.type, time

Sweet eh? As you can see this is pretty much basic stuff and there are tonnes of other useful reports that have to be created, but these are the essentials. If you’ve got your own reports to share, you’re welcome via comments ;)

Happy project managing!