Exporting a Ranked GreenHopper Backlog

[Eric Marnell]

Using Jira 5, GH 6. For our project, we have sprints set up in GreenHopper as well as a populated, ranked backlog (ranking is enabled). We have multiple product owners (yes, I know that's not the best solution), so having them go into GreenHopper and moving items around in the backlog is problematic as they may cancel out each other's sorting of the backlog issues. What I'd like to do is export the existing, ranked backlog of issues visible in GreenHopper to an Excel spreadsheet. From what I've read elsewhere on the Atlassian site, the best way to do this is to simply do an advanced search with JQL functions. I've tried the following, which seems to generate unending syntax errors:

project = "ABC" and sprint is empty and ORDER BY rank and status !=Closed

I'm thinking that will return the issues in project ABC, the issues that are not in a created GreenHopper sprint (sprint is empty), the issues returned in their ranked order as denoted in the backlog, and any issues that are not closed (meaning they are open, in progress, or resolved).

Any thoughts on my JQL advanced search? If I can get that to work, then you can view it in the issue navigator and then save it as an Excel spreadsheet. The trick is how do I fix my advanced search to accurately capture the GreenHopper backlog?

[Martin Audley]

You can also try:

filter = <YourBacklogsFilterName> and

(sprint = empty or sprint not in openSprints()) and

status not in ( Closed, Done, Rejected, Deferred, Resolved ) and

issuetype in standardIssueTypes() and

issuetype not in (Epic)

order by Rank

which gives me exactly the same list as my Backlog.

[GeorgeT]

For JIRA v6.1-OD-09-WN, the following filter worked for me:

type = story and sprint is empty and status !=Closed ORDER BY rank

(I use the filter with a particular project seleted).

Thanks for the help!

[Alexandre Wanderley]

Hi Shaun,

Your solution was very helpful. Is it possible to see the "Estimate" (Story points) in the result? How can I include this information in the filter?

[sclowes]

Hi Eric,

Your query should really work correctly, just use something like:

filter = <name of the filter used in your GH board> and (sprint = empty or sprint not in openSprints()) and status not in ( Closed, Resolved ) order by rank

You'll need to update the status part to exclude whichever statuses you have in the last column of your board.

That should yield a list that's identical to what you see on the board.

You can then just view that list in the issue navigator and hit the 'View' icon and choose 'Excel' to export the list to excel.

Cheers,
Shaun

[Eric Marnell]

Thanks Shaun. I'll give it a try, along with several other queries, on Monday to see if it works.

[Eric Marnell]

One more thought. Assuming that where you cite "name of the filter used in your GH board," that you are referring to the "Backlog" in GreenHopper? Is that actually searchable as a filter name?

[sclowes]

When you create a board a JIRA filter is automatically created to go with it, here's an example from a board we use:

The 'Filter for Product Growth Scrum' is the name of the filter itself. So if I put 'filter ="Filter for Product Growth Scrum"' in the JIRA issue navigator I'd see only issues included on that board (except that the board would also not show any issue that was not in a status mapped to one of the columns).

Cheers,
Shaun

[JoanneA]

Hi Eric,

You're right, they want the feature for future planning, but I think the functionality will enable you to create your filter too.

To create a new feature request, just login or register at http://jira.atlassian.com, go to the GreenHopper Backlog: https://jira.atlassian.com/secure/RapidBoard.jspa?rapidView=96&view=planning and create an issue as you normally would in JIRA. Then ask all your colleagues to vote on it! ;)

[Eric Marnell]

Thanks Joanne. I actually figured out how to add it as a potential feature!

[JoanneA]

Unfortunately the Sprint field in the issue is not populated by GreenHopper until the Sprint is started, so the query will include issues in upcoming sprints.

You could filter for all issues with a Rank equal to or greater than the first issue in the backlog, but the downside of this is that you'd have to first look up that Rank field, and then it would probably change each time you ran the query.

There are related features in the backlog for GreenHopper though that will solve the problem and allow you to query based on upcoming Sprints (and so also exclude issues in upcoming Sprints)

[Eric Marnell]

Hi Joanne. What are the related features in GreenHopper that you make reference to? I really don't want to have to run some Jira filters to try and capture what's in the backlog. This would be at least 1-2 days of work to do that.Can't have multiple product owners moving issues around in the backlog as they'll cancel out each others moves! Plus, it looks like they need to be project admins to move issues around (since I created the Scrum Board being used in GH)?

Ideally, I'd love to have a drop down menu that would appear to the right of the word "Backlog" in GreenHopper that would allow you to choose either "Export to Excel (with ranking intact), or "Export as a PDF (ranking intact)."

[JoanneA]

Hi Eric,

There is an issue in our backlog that would enable searching based on future sprints, and therefore also allow searches to exclude future sprints, which I think would help you:

https://jira.atlassian.com/browse/GHS-5773

Also there is a related feature request that is very similar:

https://jira.atlassian.com/browse/GHS-6036

Please add your vote to these! There is no request yet for exporting issues as you described, but you could raise one.

The product owner needs Schedule permission to re-rank issues, but doesn't need to be a project admin.

[Eric Marnell]

Hi Joanne. I took a look at those two you mentioned. They are close to what I'm envisioning. Difference is that they look like they have allocated backlog items to future (not started) sprints. I'd rather keep it all in the backlog, then rank it all, then drag into a sprint when planning comes up. What they are really doing is just future release planning, are they not? I may vote for one of them though.

How would I submit a request for a new feature? Thought never dawned on me to do that.

[Eric Marnell]

Good news and bad news. The syntax on the JQL is now correct. The bad news is that it is not accurately retrieving the issues in the GreenHopper backlog. Currently have 243 issues in the back, but the results set for that query gives me 473 issues. It's bringing in issus that are already in a sprint.

[Timothy]

Put another clause in your filter.

AND sprint_custom_field is empty

[Eric Marnell]

Wasn't sure if including that new clause in it meant that the other sprint clause could be removed. Regardless, including it generated a syntax error re that field doesn't exist (Field 'sprint_custom_field' does not exist or you do not have permission to view it. Even playing around with how I entered it in (e.g., SprintCustomField is empty) still gave the same error.

[Timothy]

Er.. Change "sprint_custom_field" to the field that holds your Sprint Id.

[Timothy]

For your JQL:

project = "GHS" and sprint is empty and status !=Closed ORDER BY rank

[dleng]

project = "abc" and sprint is empty and status !=Closed ORDER BY rank

Your ORDER BY clause is wrongly specified.