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:
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:
Oh… this is only going to work for T-SQL, and only tested on SQL Server 2005.
3 comments to Jira History Queries
Leave a Reply
You must be logged in to post a comment.