Get the Stories & Subtasks with Status not in Done or Done in current sprint

[JOY]

Hi,

I'dlike to query the Stories & Subtasks in open, future sprints and in blacklog which status is not in (Done, Won't Fix) or Done in open/future sprints.

When I tried: 

project = TIME AND issueType in (Story, Subtask) AND (Sprint in (futureSprints(), openSprints()) OR sprint = EMPTY) ORDER BY Sprint ASC, issueType ASC

It gets all the subtasks even they are Done and not in open or future sprints.

If I tried:

project = TIME AND issueType in (Story, Subtask) AND Sprint not in closedSprints() ORDER BY Sprint ASC, issueType ASC

It will not get the subtasks although all of our subtask has sprint field.

[Walter Buggenhout]

Hi @JOY,

If you want to exclude done items, you'll need to make that explicit in your filter statement. Have you tried something like this?

project = TIME AND issueType in (Story, Subtask)
AND (Sprint in (futureSprints(), openSprints())OR sprint = EMPTY)
AND Status not in (Done, "Won't Fix")

 Hope this helps!

[JOY]

Thank you @Walter Buggenhout  But it is not what I need.

I mentioned "status is not in (Done, Won't Fix) or Done in open/future sprints".

I only need to filter Done items in the closed Sprints.

[Walter Buggenhout]

Hi @JOY, no worries. That's a matter of playing around with your criteria and parentheses:

project = TIME AND issueType in (Story, Subtask) AND
((sprint in closedSprints() and status = Done) OR 
((Sprint in (futureSprints(), openSprints()) OR sprint = EMPTY) AND
Status not in (Done, "Won't Fix")))

[JOY]

Hi @Walter Buggenhout , It still loads all the issues

[Walter Buggenhout]

The query I shared should retrieve the following data (in human language):

• All issues from project TIME that are assigned to a completed sprint AND have status = Done;
• PLUS all other issues from your project that are not in status Done or Won't fix

That this does return all issues is not entirely strange. If you close a sprint, all unfinished issues are transferred to either the backlog or another sprint. So a completed sprint - by definition - only has completed issues in it (the current query basically only excludes the ones that were marked as Won't fix in that sprint, which is most likely not going to be many).

So could you try to explain - just in plain language - what you are trying to get out of the query?

[JOY]

• All issues from project TIME that are assigned to open sprints, future sprint and backlog, AND not in Won't Fix 
• No issues from closed sprints

[Walter Buggenhout]

Thx for clarifying @JOY. Try something like this:

Project = TIME AND ((sprint in openSprints(), futureSprints() AND
status != "Won't Fix") OR statusCategory = "To Do")

 Maybe I am over-simplifying things here, but trying to make it easier is often not a bad idea. The statusCategory part returns all statuses with a grey color. I suggest that approach because it should return Open, To Do, Backlog, New, ... without the need to further specify each individual status. If you know what status you use for the backlog, you could replace that part by Status = <that status> too, but that's slightly less dynamic.

You could extend the query to explicitly exclude issues from completed sprints like this:

Project = TIME AND ((sprint in openSprints(), futureSprints() AND
status != "Won't Fix") OR statusCategory = "To Do") AND
sprint not in closedSprints()

But if it does not make a difference in results, you could just as well leave that out.

[JOY]

Hi,

Your query showed error so I have to rewrite it:

Project = TIME AND issueType in (Story, Subtask) AND (sprint in (openSprints(), futureSprints()) AND status != "Won't Fix") OR statusCategory = "To Do"

However, it doesn't show the issues in the backlog.

If I added OR Sprint=Empty, it will show all the closed issues.

Project = TIME AND issueType in (Story, Subtask) AND (sprint in (openSprints(), futureSprints()) AND status != "Won't Fix" OR Sprint=Empty) OR statusCategory = "To Do"

However, it doesn't show the issues in the backlog.

[Walter Buggenhout]

Sorry, my bad: I missed a parenthesis in my query. It should have been like this:

Project = TIME AND issueType in (Story, Subtask) AND
((sprint in (openSprints(), futureSprints()) AND
status != "Won't Fix") OR statusCategory = "To Do") AND
sprint not in closedSprints()

When you say the result does not show issues in the backlog, can you explain what status(es0 the issues in your backlog have?

I am getting the feeling that your project is team managed and "the backlog" is a concept you cannot really query for. So it would make sense to query on the status(es) that are used there.

[JOY]

Yes I use team-managed project. The issues in my backlog could have different status: To do, In Progress, etc.

[Walter Buggenhout]

Then we should be very close with this one:

Project = TIME AND issueType in (Story, Subtask) AND
((sprint in (openSprints(), futureSprints()) AND
status != "Won't Fix") OR statusCategory != Done) AND
sprint not in closedSprints()

[JOY]

Nothing different. It doesn't show any issue in the backlog (only show issue with a sprint)

[Walter Buggenhout]

Okay; radical change. Let's forget about all this sprint stuff, as it seems that is causing the issue.

Project = TIME AND issueType in (Story, Subtask) AND
(status != "Won't Fix" OR statusCategory != Done) AND
sprint not in closedSprints()

[JOY]

Same result, @Walter Buggenhout 

Btw, it only shows 1 subtask which is not correct.

[JOY]

Hi @Walter Buggenhout . Could you please check?