Jira JQL query to return all items with 2 or more sprint values

[Ryley Fischer]

As a scrum master I want to see a report on items that have carried over from one sprint to another so that I can coach the team on user story estimation.

Acceptance Criteria:

A JQL query that returns all items that have a sprint value great than or equal to 2.

I have a query that gets me close to what I want, but it doesn't quite return everything I'm looking for. 

project = "Project Name" AND issuetype not in ("Sub Test Execution", Subtask) AND sprint is not EMPTY AND sprint in closedSprints() AND sprint in openSprints()

This will only return items that have more than one sprint value, and are in the active sprint. I want to also see items that were in two closed sprints.

[Mikael Sandberg]

There is not function in JQL that would allow you to do do this, and I don't think any of the apps that extends JQL has a feature for it either. What you could do is have a custom field that keeps track on the number of sprints the issue has been in and then have an automation that updates that field every time a new sprint is started.

[Bill Sheboy]

Hi @Ryley Fischer 

Yes, and...to Mikael's answer:

If you decide to add the custom field and add an automation rule, you can run this for issues from prior sprints to determine if this is an older symptom or a newer one. 

For example...

• trigger: scheduled (e.g., every hour), with JQL to find issues in your project where the custom field is empty and the issue is done/resolved
  • project = myProjectName AND myCustomField IS EMPTY AND statusCategory = Done
• action: update your field with {{issue.sprints.count|0}}

A rule like this will process 100 issues at a time and then stop.  It will keep triggering until there are no more issues to process, and then you can disable it...or leave it enabled to handle as sprints complete.

And for a symptom like this which is hopefully a temporary condition, you can stop using the rule and custom field once the team improves how they plan, focus, deliver, etc. and reduce carry-over.

Kind regards,
Bill

[Hannes Obweger - JXL for Jira]

Hi @Ryley Fischer

if you're open to solutions from the Atlassian Marketplace, this would be easy to do in the app that my team is working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of so-called "smart columns" that aren't natively available, including the number of sprints (along with many other sprint-related columns).

This is how it looks in action:

As you can see above, you can easily sort and filter by this field, and also use it among JXL's many advanced features, such as issue grouping, sum ups, or conditional formatting. Once you've narrowed down your list of issues, you can work on your issues directly in JXL, trigger various operations in Jira, or export your issues with just one click.

Note that this "just works" - there's no scripting or automation whatsoever required.

Any questions just let me know,

Best,

Hannes