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

JOY May 7, 2023

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.

1 answer

0 votes
Walter Buggenhout
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 8, 2023

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 May 8, 2023

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 8, 2023

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 May 9, 2023

Hi @Walter Buggenhout , It still loads all the issues

Screenshot 2023-05-09 210519.png

Walter Buggenhout
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 9, 2023

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 May 9, 2023
  • All issues from project TIME that are assigned to open sprints, future sprint and backlogAND not in Won't Fix 
  • No issues from closed sprints
Walter Buggenhout
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 9, 2023

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 May 10, 2023

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 10, 2023

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 May 10, 2023

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

Screenshot 2023-05-10 204835.png

Walter Buggenhout
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 10, 2023

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 May 10, 2023

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

Walter Buggenhout
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 10, 2023

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 May 10, 2023

Same result, @Walter Buggenhout 

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

JOY May 12, 2023

Hi @Walter Buggenhout . Could you please check?

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events