Jira Release Notes and Perforce

┬áSomeone recently asked me if the release notes in JIRA could contain the files that have changed to fix that issue. Perfectly reasonable. However, the changelists (the $client uses Perforce) that fix the issue are not held in JIRA, instead, the plugin runs some p4 commands to get the changelists, and in turn each delta that is part of that changelist. It also does some nice stuff such as turning it in to link to p4web so you can click through to see the diffs. I considered re-using the code from the Perforce plugin, but…

I’m not a big fan of the release notes feature in jira. It’s too restrictive, although you can add templates for release notes, they’re available to everyone, and then they can’t be configured further, much. Everyone wants a different format, with different custom fields and so on. At the least the release note feature should support standard features of the query navigator. The feature of copying the HTML is a bit gimmicky IMHO.

I wanted to do this in SQL Server Reporting Services, that way, I could parameterise the report, publish it, and other people could modify it if they wanted. It also supports export to various formats, which is a common requirement.

I thought this would be a straightforward enough task as Perforce has an ODBC driver, and RS can consume any ODBC data source.

To get the list of issues fixed by a release is simple enough:

loading http://blogs.onresolve.com/jechlin/41/getIssues.sql…

Release is a parameter that is supplied by the person running the report, and passed in to the query.

For each of these I have a subreport, which runs a query against Perforce to get each changelist comment, author, and file and revision associated with those changelists. You’d hope to be able to write a parameterised query such as:

loading http://blogs.onresolve.com/jechlin/41/p4q1.sql…

If you do you will get:


After much soul-searching, I came to the conclusion that this was because the Perforce ODBC driver does not support named parameters (although rerunning it in Visual Studio just now, there is a message box that tells me exactly that).

Instead, the SQL needs to be wrapped up as an expression that is re-evaluated every time the sub-report is called:

loading http://blogs.onresolve.com/jechlin/41/p4q2.sql…

You end up with something like:


Actually you end up with something a lot more more interesting, I just cobbled that together as an example for a picture that didn’t contain anything potentially useful to social engineers.

Jira Release Fields

Unfortunately jira does not allow you to specifiy additional metadata for Projects and Versions (JRA-1991). In terms of Versions/Releases, people want to be able to specify estimated and actual release dates, customers, owner various. In some cases there are requirements for Releases to have their own workflow, where it is tested in various environments (as distinct from simply testing each issue in isolation), and then gets built, signed off, end-of-lifed and whatnot. For those unfortunate enough to have experience with ClearQuest you may even expect this.

The solution I’m using (I think I got this idea from someone else, but can’t find my source now), is to have an issue type called Release. I give it the workflow and metadata as required by the process of the team in question. Then for every version created, you need to also create a Release issue with a corresponding summary. This part is a bit disjointed, have not thought how to make this happen automagically yet.

Then it’s easy enough to get what fields you need from the release record:

loading http://blogs.onresolve.com/jechlin/41/p4q3.sql…

Jira – Reporting Services Integration

If you come up with some nice reports you will want people to see them. Unfortunately they don’t like to remember different URLs, so embedding it in Jira is a good idea. I was toying with the idea of writing or adapting a portlet plugin that would just fetch some page and output the HTML, however I was bound to come up against problems when the target URL required NTLM or kerberos integration.

Fortunately I stumbled on a simpler solution. Once you’ve enabled the text portlet plugin, you can create portlets that display arbitrary HTML, such as a list of reports on your reporting services server, allowing people to click through. When you combine this with jira dashboard sharing, that gives you quite a powerful tool in your reporting armoury. Another use of the Text portlet is to display an iframe, which can pull your report directly from RS.



It’s helpful to know something about parameters that you can pass to reporting services, so you can hide the toolbar and so on.

2 comments to Jira Release Notes and Perforce

  • Slick graphic, dude! And a nice piece of customized work (my clients always want tweaked reports too). I did a custom release notes plugin a while back to produce DocBook text, but it still needed review before use by the tech pubs department – maybe that’s why Atlassian provided the cut-and-paste text? I’m always a bit wary of using SQL directly to get the data, since the required query can change from version to version, and database to database. Sometimes, as in the timecharts plugin I wrote, it seems to the be the best approach.

  • I agree about the customisation, hence the cut’n'paste text – i guess i’m used to just doing View Source and scraping what I need. No accounting for managers’ ability though, eh ;-)

    You’re probably right about direct SQL access – it’s just a gazillion times faster than writing the equivalent in java.

Leave a Reply