How can I create a filter to show me the entire backlog for my team?

[leelabeela]

Can anyone help me understand why my query isn't providing the expected results?

I'd like to create a filter to show me all tickets that are not in the current open sprint. We do have some placeholder sprints created that are used largely for organizing tickets before they're assigned to actual working sprints.

If I create the query the following way the parameters for scrum team and status are ignored in the results I get and I end up seeing a significantly higher number of results than I should:

"SCRUM Team (Agile)" = Revolution AND sprint IS EMPTY OR sprint NOT IN openSprints() AND status in (Open, "In Progress", Reopened, New, "On Hold") and project = Development

If I try to create the query based on tickets that are not in the active sprint I get significantly fewer results than I should:

project = BLUE AND status in (Open, "In Progress", Reopened, New, "On Hold") AND "SCRUM Team (Agile)" = Revolution AND Sprint not in openSprints()

This query also provides far too many results and seems to ignore the scrum team and status parameters.

project = BLUE AND status in (Open, "In Progress", Reopened, New, "On Hold") AND "SCRUM Team (Agile)" = Revolution AND Sprint not in openSprints() OR Sprint is EMPTY

How can I correct this? 

[Nic Brough -Adaptavist- · Accepted solution]

I suspect this is about the clauses you are joining with OR. The usage of AND in a query is simple and intuitive, but OR is not. You need to look at the order and what joins what to what.

"A and B and C" will give you the same as "B and A and C", but "A or B and C" is a different question to "A or C and B", and nowhere near as easy to work out what it is doing!

I usually find it easier to use braces to explain it.

project = BLUE AND status in (Open, "In Progress", Reopened, New, "On Hold") AND "SCRUM Team (Agile)" = Revolution AND Sprint not in openSprints()

I would expect to be giving you an answer you understand.  But if I have understood your requirement correctly (be aware I'm not good at that!), I would change

project = BLUE AND status in (Open, "In Progress", Reopened, New, "On Hold") AND "SCRUM Team (Agile)" = Revolution AND Sprint not in openSprints() OR Sprint is EMPTY

to

project = BLUE AND ( ( status in (Open, "In Progress", Reopened, New, "On Hold") AND "SCRUM Team (Agile)" = Revolution AND Sprint not in openSprints() ) OR Sprint is EMPTY )

I'm less sure what to do with your first query as the project is different and the OR elsewhere but maybe

project = Development AND "SCRUM Team (Agile)" = Revolution AND (sprint IS EMPTY OR sprint NOT IN openSprints() ) AND status in (Open, "In Progress", Reopened, New, "On Hold")

So I'd try breaking up queries with braces and evaluating each brace-pair in turn.

[leelabeela]

I think this one did it! Thank you!!!

I'm going to dig through the results further and confirm. This provided about 500 results while all of the others gave either around 200 or 4,000+. I'm not sure why the project ended up different in one of the queries. For our setup when I let JIRA suggest the syntax it says Development (BLUE) so using either as project seems to give the same results. 

project = Development AND "SCRUM Team (Agile)" = Revolution AND (sprint IS EMPTY OR sprint NOT IN openSprints() ) AND status in (Open, "In Progress", Reopened, New, "On Hold")

[Alex Christensen]

You might try adding this to your queries:

Sprint not in openSprints() AND Sprint is not EMPTY AND Sprint not in closedSprints()

This should get rid of everything that's in closed sprints, too.