Export Jira Backlog to Excel

[Krisna Leo Parista]

Hello,

I am a newbie as a scrum master. I exported all my active sprint into excel via filter JQL,

but then I do not know how to export Backlog Jira into excel, can't find in filter JQL.

Could somebody please help me? Thank you so much! 



Cheers,
Krisna

[Jack Brickey · Accepted solution]

You can create a JQL filter in the Advanced Search that equates to your backlog and export from there. Assuming your backlog consists of a status of “To Do” then it would look like this

project = abc and status = “To Do” and sprint not in opensprints()

[Krisna Leo Parista]

Yup, this is working.. thank you so much Jack :)

Cheers,
Krisna

[Susan Parker]

I have the same issue, but I'm not very good with JQL, and this isn't working for me (after I replace 'abc' with my project name, and status = "Open").

With this...

project = "project name" AND status = "Open" AND sprint not in opensprints()

... my results are 19 issues that ARE in sprints (status = open) and not in the backlog (227 issues).  

PS:

Also, how would I construct this query if items in the backlog have some statuses other than open, like "pending information" - just use "OR" between status ='s? 

[Michael P]

Hi @Susan Parker. The above didn't work for me either. You might try the following, tweaking as necessary:

project = projectName AND status = "To Do" AND resolution = Unresolved AND Sprint is EMPTY and type != Epic

project = projectName AND resolution = Unresolved AND (Sprint not in openSprints() OR Sprint is EMPTY) AND type != Epic ORDER BY Rank ASC

Note the "ORDER BY Rank ASC" which, for me at least, puts the items in the order they are displayed in the backlog. "Sprint = EMPTY" instead of "is" worked for me, but not others. As Jack says below, this query may not fit your unique situation, but it should be a place to start.

[Jack Brickey]

Hi @Michael P , one potential hole in your JQL is with Sprint is empty. The reason why this could be problematic is undo the following scenario. Let's say issue 123 what is a sign to a sprint and not completed. In closing the sprint the issue is then move to the backlog at that point the sprint Field will not be empty and yet the issue is in the backlog. I hope this makes sense.

The exact JQL well that will work for you or anyone Will depend on a number of factors. So it is difficult to say for sure what will work without discussing it with the individual.

[Michael P]

I just ran into that edge case myself right before you replied! Right you are.

I've edited to show my updated query. The issue with "not in openSprints()" seems to be that it assumes you mean it is in some sprint, so including both cases with an OR should catch what we're looking for

[Susan Parker]

@Michael P @Jack Brickey Thank you both for posting - I will give this a try and report back.

[Jack Brickey]

slight correction use "is" rather than "="

project = projectName AND resolution = Unresolved AND (Sprint not in openSprints() OR Sprint is EMPTY) AND type != Epic ORDER BY Rank ASC

[Susan Parker]

Awesome, with a couple of small tweaks, this worked !!!!  ('is' v. '=' didn't seem to make a difference)

[Susan Parker]

And just noticed these buttons - have they been there all this time? A slightly better export than CSV for Excel or Google Sheets.

[Jack Brickey]

That image appears to be from the advanced search not the Backlog view. But, yes those have been there for many months now.

[Jack Brickey]

BTW… “=“ does not work for me. If it does for others I cannot explain why.

[Michael P]

Using "=" instead of "is" works for me, though I'm on an older Server instance of JIRA. Can't explain why it works for Susan, but I'll edit again to be kind to future readers

[Susan Parker]

Yes, image is from advanced search view. Still have to formulate query.  It's just a slightly better export option than CSV. Didn't notice till today.

RE = / is - I am on cloud 'on demand' version. 

[Jack Brickey]

Hi Susan,

Yes I'm on cloud as well and remain puzzled about the "=".

One other thing you may be interested in is this extension for Excel - jira-cloud-for-excel-official . It is free and a simple extension for excel that allows you to run queries directly from excel into your Jira instance. This has some nice advantages even over the export icons you mention. Note that there is also a Sheets version of this too.

[Danno]

Regarding this thread, I have found this documentation reference a great place to start to hone your search skills using JQL.

https://support.atlassian.com/jira-service-management-cloud/docs/advanced-search-reference-jql-operators/ 

TO answer the specific question regarding = / IS it is on the page I linked but it states this:

EQUALS: =

The "=" operator is used to search for issues where the value of the specified field exactly matches the specified value. (Note: cannot be used with text fields; see the CONTAINS operator instead.)

To find issues where the value of a specified field exactly matches multiple values, use multiple "=" statements with the AND operator.

IS

The "IS" operator can only be used with EMPTY or NULL. That is, it is used to search for issues where the specified field has no value.

Note that not all fields are compatible with this operator; see the individual field reference for details.

[Susan Parker]

@Danno Very helpful, thanks!

[Aron Gombas _Midori_]

You can use the Better Excel Exporter app to export issue directly from the backlog screen to custom Excel spreadsheets. That way you can save manually crafting JQL queries, although that's also supported because you can export from Issue Navigator as well.

(Disclaimer: I am developer working on this app. It has been available for Server and Data Center for many-many years, and we released the Cloud version literally yesterday!)