The ELC Community Blog

A knowledge exchange on Ruby on Rails and Agile Development

Points and Velocity in Trac Reports

by Asa Wilson on November 09, 2007

Most Agile methods use some sort of complexity metric to track the relative time necessary to complete a task or story. Whether you're using developer days or story points, you can use the severeties field in trac to monitor your progess. Here's how:

*The following should only need to be completed once per Trac system and will require admin rights.

Adding the Points to the system

1. Click on the "Admin" link in the upper right hand corner (if you don't see it, you don't have the correct permissions).

2. On the left hand side of the screen you will see a menu of links. Click on "Severities" under the Ticket System heading.

3. The interface here is pretty intuitive, just type a Point value into the text box on the right and click "Add." Repeat until done. You should probably add both "0" and "??" values for no-point-value bugs and unknown difficulties, respectively.

Adding the column to your reports

1. Click on "View Tickets" in the menu on the upper right.

2. Select whichever report you'd like to modify.

3. Click on the "Edit Report" button. This will take you to a form where you should see the underlying SQL for the report (Under the header "SQL Query for Report", conveniently enough).

4. Look for the "summary" item in the select clause of the query (if it's not there, just choose an appropriate position in the list). Insert the following text prior to the summary item:

severity AS points,

5. Click "Save Report"

Assuming you don't receive a SQL error, you should now see the original report with a "Points" column added.

New report: Velocity

Alright so this is all fine and good but now the real fun begins. Since you have a points field you can now use the Custom Reports in Trac to analyze your progress in relation to these points. A popular report in the Agile world is Velocity (or the number of points you complete per iteration). Without further hullabaloo lets get into it... (Again assuming you have Admin privileges or mad hacking skills)

1. Go to "View Tickets" on the Nav bar.

2. Click on "Create new report"

3. Name your report something creative like "Velocity Report".

4. Enter the following code into the SQL Query for Report:

SELECT t.milestone AS __group__,
      COUNT(id) AS tickets, SUM(severity) AS points
      FROM ticket t,enum p
      WHERE (resolution IS NULL or resolution NOT LIKE 'duplicate')
      AND p.name=t.priority AND p.type='priority'
      AND status = 'closed'
      GROUP BY t.milestone
      ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'),
      (-1)*p.value DESC

5. Click "Save report" and enjoy your new data harvesting masterpiece.

Well there you have it. "Points" added to all you reports and a new report which gives you a velocity per iteration. Way cool!