Jira History Queries

Someone asked me how they can get a chart like the one below, but broken down by priority.


That is, they want the average numbers of days open over time, by priority. Incidentally, I’d like to know why it doesn’t display dates along the bottom, instead you get those dots.

Probably the correct way to do this would be to modify the java for this portlet, and create a new JFreeChart format, however that seemed way too complex so I decided to do it in SQL, and use Dundas Chart and Reporting Services to render it. This can then be embedded in a dashboard using the method I mentioned in a previous post (i.e the Text portlet and an iframe).

What I ended up with was:


which is close enough to the first chart to make me think I got the SQL right. This takes a couple of parameters, the project key and number of days previous. The SQL is:

loading http://blogs.onresolve.com/jechlin/50/DefectAgeBySeverity.sql…

I create a calendar with all the dates over the period we’re interested in, “select number from master..spt_values where type = ‘p’” is a little trick to get a sequence of integers.

Having done this it’s easy enough to get the count of issues rather than average age:


SQL is:

loading http://blogs.onresolve.com/jechlin/50/DefectCountByPriority.sql…

Oh… this is only going to work for T-SQL, and only tested on SQL Server 2005.

3 comments to Jira History Queries

  • justin

    I’m trying to do something similar with JIRA 4 — using SQL to pull average time in status. The JIRA Charting Plugin does not allow for tracking only working days/hours. Could you elaborate on modifying your TSQL queries to use SQL to display this kind of data?

  • Not sure how you need it elaborated? I didn’t use the charting plugin, I used dundas. To be honest this was a long time ago and I don’t particularly recommend this approach as you break portability between databases, and risk sql injection attacks etc. Still, it’s the only option for some requirements.

  • This is clever SQL. I wrote the Timecharts plugin because getting this right in the general case for any field was too hard in SQL.

Leave a Reply