Show issues sorted by the sprint they got completed in

[Andrea Kunkel]

I'm struggling to find a way to show issues filtered/sorted by the sprint they got completed in.

What do we want to show?

We have a dashboard where we show issues with the label "overrun". We're showing this for each sprint so we can see how many issues with this label got completed in each sprint.

Simplified Example:
Sprint 1: 5 completed issues were "overrun".
Sprint 2: 2 completed issues were "overrun".
Sprint 3: 8 completed issues were "overrun".

A lot of our issues don't get completed in the sprint they got started in so are in several sprints.

How did we set it up?

We have a custom field called "Resolved in sprint". For this field, we have the following automation set up:
When an issue gets transitioned to "Complete" the value of the field "Sprint" (last item in the list) gets copied to the field "Resolved in sprint".

This way we know which sprint an issue got resolved in and can show it on the dashboard mentioned above.

This is the problem:

This works well for issues that are only in one sprint. But when the "Sprint" field has more than one item it's causing issues. I thought I could solve it by using the last item in the field {{issue.sprint.last}}.

Unfortunately, Jira doesn't show the list of sprints in chronological order.

E.g. For one of our issues it looks like this:
"Sprint 62, Sprint 63, Sprint 64, Sprint 58, Sprint 59, Sprint 60, Sprint 61"

The sprint it got completed in is "Sprint 64" (the sprint with the highest number). I expected it to show last in the list but it seems quite random.

Question:

Is there any way to show which sprint an issue got completed in when it's been in several sprints?

Or is there a way to re-order the list of sprints (e.g. by sprint ID or creation date)

Any help appreciated. If there's an easier way to get this information instead of using a custom field and automation I'd be very happy to hear about it.

[Marlene Kegel - codefortynine · Accepted solution]

Hi @Andrea Kunkel,

I am Marlene from codefortynine.

Our Marketplace app Dynamic Custom Fields might be useful for you. This app enables you to compute dynamic values through Jira Expressions.

For instance, you can generate a field that automatically replicates the name of the sprint where an issue was marked as "done".

While we provide numerous templates to simplify the configuration of your dynamic custom fields, a customized expression would be required to meet your specific needs.

• After installation simply navigate to the Dynamic Custom Fields configuration page
• Copy/paste the Expression below in the "Expression field"
• Make sure to use the correct resolution name (you might have to exchange "Done" with the resolution name used in your project)
• Check the field value preview on the right side to make sure that the correct sprint is displayed
• Give it an arbitrary name (e.g. "Resolved in Sprint")
• Click "save"

(issue.closedSprints.length >= 1 || issue.sprint == null) && issue.resolution?.name == "Done" ? issue.sprint?.name ?? issue.closedSprints[issue.closedSprints.length - 1]?.name : "" 

Now you can add the dynamic custom field to your project as any other custom field.

If you have issues with the expression or any other questions or feedback, don't hesitate to reach out to us.

[Andrea Kunkel]

@Marlene Kegel - codefortynine Thanks a lot. From your reply, it sounds like this might be just what we're missing in Jira and could also help us with other reports.

I tried your Expression but it doesn't seem to be working. Would you mind checking if I did anything wrong?

This is what I did:

• copy/pase your expression
• change status name from "Done" to "Complete"

In the preview, I'm filtering issues from our current sprint (ID 114) that are Complete. So I'm assuming all these issues should now have the current sprint as the Field value but it shows as empty. Any ideas why this isn't working?

[Marlene Kegel - codefortynine]

Hi @Andrea Kunkel,

Unfortunately,  I'm not able to identify the problem at the moment.

Would you mind contacting our support team? They can delve deeper into the matter and provide you with more information.  

Apologies for not being able to resolve the issue immediately, but I'm confident that our support team will be able to assist you and resolve the matter. :)

[Marlene Kegel - codefortynine]

@Andrea Kunkel I believe I've identified the issue. The expression I shared earlier was intended for the "resolution" field, not for the "status" field. To resolve the problem, you need to input the correct resolution name.

In my previous response, I mistakenly referred to the "status" field, which was my error. I have since corrected in my answer above. 

If you prefer to use the "status" field instead, you can easily replace "resolution" with "status" in the expression.

(issue.closedSprints.length >= 1 || issue.sprint == null) && issue.status?.name == "Done" ? issue.sprint?.name ?? issue.closedSprints[issue.closedSprints.length - 1]?.name : ""

[Andrea Kunkel]

@Marlene Kegel - codefortynine Thanks so much, that solved it!

Just one more issue: For subtasks, the correct sprint doesn't show but it seems to show the sprint the parent issue/task got completed in. Is there any way to show the actual sprint the subtask got completed in?

Example:
- Parent issue with subtask got added to sprint 1.
- Subtask got completed in sprint 1.
- Parent issue got moved to sprint 2 (which also moves the subtask).
- Parent issue got completed in sprint 2.
Parent issue should show "sprint 2".
Subtask should show "sprint 1".

[Marlene Kegel - codefortynine]

It's wonderful news, @Andrea Kunkel ! You're most welcome.

To address your second question, I'll need to consult with a colleague who has more expertise with Jira expressions. 

Alternatively, you could directly contact our support team, as they may be able to respond to you more quickly. :) 

[Andrea Kunkel]

@Marlene Kegel - codefortynine I'll reach out to your support team

[Marlene Kegel - codefortynine]

Hi @Andrea Kunkel

I trust that our support team was able to resolve your issue. Additionally, I'm including the solution here to benefit anyone following along.

This expression should be more flexible:

issue.resolutionDate != null 2? issue.closedSprints?.filter(c => c.completeDate > issue.resolutionDate && c.startDate < issue.resolutionDate)[0]?.name 3?? issue.sprint?.name 4: ""

Thank you for your feedback, @Andrea Kunkel . 

[Trudy Claspill]

Hello @Andrea Kunkel 

Before I try to offer information to help solve your dilemma I have to first say that the real question you need to be addressing is the problem of issues not getting completed in the sprint in which they were started.

The idea of agile scrum process is to plan your sprint, commit to only the work you can get done within the sprint, and then do your utmost to focus exclusively on getting that work completed.

You should being doing sprint retrospectives to identify why the work is not getting done within the sprint, and address those issues. Is the work not scoped accurately and actually requires more effort than can be completed in the sprint? Is your team being pulled away from the work by other, unplanned work? Are your priorities being changed? Are you simply committing to more than you can possibly get done?

Without addressing the root problem, some of the benefits of scrum (knowing the team velocity, reliably meeting commitments, confidence that commitments can be met) will be beyond your team's grasp. I've worked in teams like that. It can be frustrating and demoralizing. I strongly encourage you to get to the root of the real problem and if necessary be an advocate for your team with you leadership organization to get the problem addressed.

Getting off my soap box now. :)

There have been a few posts in the communities that relate to this topic.

Here are a couple that are focused on wanting to know which of the sprints for a given issue is the current, active sprint.

https://community.atlassian.com/t5/Jira-Software-questions/Get-active-sprint-in-automation/qaq-p/1368655

https://community.atlassian.com/t5/Jira-Software-questions/Get-active-sprint-name-in-smart-value-for-automation/qaq-p/1573065

The excellent suggestion in both cases from @Bill Sheboy was to use another custom field to keep track of the current active sprint, using Automation to set the field in each issue when the sprint is started. If issues are added or removed from active sprints you need more automation to detect that and clear/set the field the field. You can then use Automation again when the sprint is closed to copy the "active sprint" value to the "closed in sprint" field for completed issues.

I have not found any posts that offer a solution for retroactively determining in which of multiple sprints an issue was actually closed. You may want to investigate third party apps from the Atlassian Marketplace to see if there are any solutions there.

[Andrea Kunkel]

Hi @Trudy Claspill 

Thanks a lot for linking the two posts, I already had a quick read and will look into them in more detail now. Hopefully, the answers to these posts can solve our problem.

I tried to find related posts but I guess I didn't use the best search terms. I'm always impressed by how much information is available in the Community forum here. Sometimes, it just takes a little help to find it. :)

Regarding your other feedback: Thanks, I appreciate it.

I understand what you're saying about getting issues completed in the planned sprint but unfortunately, that's not always possible in an agency environment. We need to be a bit more flexible and sometimes add new urgent issues to a sprint (and remove others to consider capacity/workload) and for many issues, we need client feedback which can take longer than a sprint.
So having a great plan is always good but reality can look different.

We also do sprint retrospectives, that's exactly what we need the dashboard I'm having issues with for.

[Trudy Claspill]

Hm, well, for the tasks where you have to wait on feedback perhaps you could break that into two tasks, like "initial implementation" and "rework after feedback" so that you can track the work where you control the definition of "complete" vs. being held hostage to waiting on feedback.

[Hannes Obweger - JXL for Jira]

Hi @Andrea Kunkel

I understand that you're currently looking into an Automation-based solution, and that's definitely a feasible direction!

Just to expand on Trudy's note on the Marketplace: If an app may be an option for you, I believe that your use case would be easy to do with the app that my team is working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view 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 last sprint (along with several other sprint-related columns).

This is how this looks in action:

As you can see above, you can easily sort and filter your issues by the last sprint. 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.

The last sprint is also usable across JXL's advanced features, which include the ability to perform (nested) issue grouping, calculate sum-ups across these groups, and apply conditional formatting. I'd actually be very confident that you could model your entire report in JXL.

Any questions just let me know,

Best,

Hannes

[Andrea Kunkel]

Hi @Hannes Obweger - JXL for Jira 

Thanks a lot for your reply. Your app sounds like a powerful, useful tool, definitely worth looking into.