How to build a backlog filter query using JQL

[Adam Tiner]

I am trying to create a filter that will show me items that are in the backlog, but am having difficulty.  I hope that someone can help point me in the right direction, and that it is not an issue of JIRA lacking another feature that I need!

My current filter looks like this:

status != Done AND (Sprint not in (openSprints(), futureSprints()) OR Sprint is not EMPTY) ORDER BY createdDate ASC

If I change it to say only

Sprint not in (openSprints(), futureSprints()

then I get results that are not in the current sprint - exactly what I need.  The only problem is that it excludes items that have previously been assigned to a sprint, but were never completed.  If I change it to say only

Sprint is not EMPTY

then I get results that have been previously assigned to a sprint - again, exactly what I need!  The only problem is that it excludes items that have never been assigned to a sprint.

When I try to combine the two inside of a parenthesis grouping, I get most of what I need - but for some reason, it is including the items that are in my current (active) sprint.

I feel like this would all be solved by having Atlassian implement a simple "currentSprint()" operator, so that I could say

Sprint not in currentSprint()

Can anyone help me out - or am I out of luck?

[Boris Georgiev [Appfire] · Accepted solution]

What about the JQL below ?

status != Done AND (sprint is EMPTY OR (sprint in (closedSprints())))

Should return all issues

• NOT Done
• Not in ANY sprint
• OR
• In sprints that are complete

[Adam Tiner]

Hi Boris, thanks for responding!  This definitely gets me much closer to where I need to be.  There are a few items that I'd still like to get rid of though; I see issues carried over from our last sprint (closed) that have been assigned to the current sprint.  I really appreciate you taking the time to reply!

[Boris Georgiev [Appfire]]

So you want to exclude the items from the current sprint right ?

what about this query:

status != Done AND ((sprint is EMPTY OR (sprint in (closedSprints()))) AND (sprint NOT IN (openSprints())))

[Adam Tiner]

Yes.  Essentially, I want to...

Include:

• Items that have never been assigned to a sprint
• Items that have been assigned to a previous sprint

Exclude:

• Items in a "Done" status
• items in the current sprint
• Items in future sprints

It seems like the query above is filtering out items that have never been assigned to a previous sprint (i.e. sprint is EMPTY), but I actually need them to be included!

Thanks again for taking the time to respond!

[Boris Georgiev [Appfire]]

Here's a query similar to the one above with some explanation:

sprint IS EMPTY OR sprint IN closedSprints()  AND sprint NOT IN (openSprints(), futureSprints()) AND status != Done

Include 

sprint IS EMPTY  OR  sprint IN closedSprints()

• Items that have never been assigned to a sprint (sprint is EMPTY) 
• Items that have been assigned to a previous sprint (sprint in closedSprints())

Exclude

sprint NOT IN (openSprints(), futureSprints()) AND status != Done

• Items in a "Done" status (status != Done)
• items in the current sprint (sprint NOT IN openSprints())
• Items in future sprints (sprint NOT IN futureSprints())

[Boris Georgiev [Appfire]]

I've corrected my comment above - please try without all the brackets.

[Adam Tiner]

This is absolutely what I needed!  I owe you a coca-cola!  Thanks very much for your patience, and also for taking the time to explain it to me in crayon.  

Much obliged!

[Sujeet Hotta]

This definitely helps me as well !! Thank you..

[Adam Tiner]

Bumping to see if anyone else has another suggestion.  I would be surprised if I were the only one who needs a filter like this. I want to be able to setup a monthly subscription that emails folks with items in the backlog.  Am I missing something obvious?

[Eric Smith]

I'm in the same boat.  It's absurd that I can't see what's in my backlog at a glance.  Right now I'm using this:

project = NAME and sprint IS EMPTY and status = open OR project = NAME and sprint IN closedSprints() AND sprint NOT IN (openSprints(), futureSprints()) AND status = Open

[Nick Ayala]

Hey @Adam Tiner 

I ran into this same issue and its been a while since you asked this question. I am posting this answer in case anyone else runs into this.

In order to target issues that are in your backlog but not within a future sprint, open sprint, or that was in a closed sprint but was unresolved use this query

Sprint in futureSprints() or Sprint is EMPTY or (Sprint in closedSprints() AND resolution = Unresolved and Sprint not in openSprints()) 

Now you will need to make sure to filter out all issues that do not have a status of Done or are unresolved

status != Done 
or
resolution = Unresolved

So the full query would look like this

Sprint in futureSprints() or Sprint is EMPTY or (Sprint in closedSprints() AND resolution = Unresolved and Sprint not in openSprints()) and issuetype in (your issue types here) and resolution = Unresolved

I hope there is an easier way of doing this but the only solution I could come up with. I hope this helps someone.

[Simrat pal Singh]

to me this works nicely project = NAME and  (sprint not in openSprints() or sprint is empty)