How to Use Conditional Statements in Search Filter

Joshua Balsillie July 24, 2018

Challenge

Make a dashboard that shows the current sprint, or next sprint if there is no active sprint.

  • dashboard has direct relationship with a board
  • Using Jira Cloud

 

Purpose

Monitor current or upcoming tasks depending on sprint status in a single view.

 

What I Have Now

project = "Project Name" AND Sprint in openSprints()

The problem with this is the dashboard is empty when no sprint is in progress.

filter in ("Filter A") AND [...]

I am using this technique at the moment for many of my filters.

 

What I am Trying to Achieve 

[if openSprints != null]
Sprint in openSprints()
[else]
Sprint in futureSprint() / futureSprint().[0]

Unfortunately I don't think Jira supports conditional statements natively in search.

 

What I Have Found

IF statement in JQL Filter

Advanced searching - functions reference

Search JIRA like a boss with JQL

Adding a JQL function to Jira (believe for Jira server only)

 

Thoughts

Because sprints can't be assigned start and end dates prior to starting a sprint, I'm thinking Jira may not have criteria defined for "next sprint".

I'm open to plugins, and even learning to make JQL functions (though less desirable).

I know for open / future sprints I could have:

  1. Single dashboard with double the widgets (50% empty if no sprint in progress)
  2. Two dashboards (one dashboard 100% empty if no sprint in progress)

2 answers

0 votes
Laura Santucci June 8, 2022

Catching up on this very old conversation since I was looking for myself and found a way around. 

@Joshua Balsillie if you haven't found a solution or for anybody looking at this. 

You can use a combination of AND/OR for the same result. In this case it would look like :

project = "Project Name" AND (Sprint in openSprints() OR (openSprints is EMPTY AND Sprint in futureSprints())

Hope it helps! 

0 votes
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
July 27, 2018

Natively, Jira can't do this kind of conditional searching in JQL.   It might be possible for you to use something like the SQL for Jira Driver: Convert JQL to SQL to help here.  This plugin lets you use more SQL like syntax when querying Jira.   So maybe that might help in your current approach.   However, this plugin is only available for Jira Server, and not for Jira Cloud.   Sorry about that.

But aside from that aspect, I am curious about a few different ideas here.  For example, if the first query is empty and you want to show issues from the future sprints, is it then too much data to see both current and future issues in sprints in this dashboard?  I'm guessing the answer is Yes, in which case ignore this idea, but my thought was to tweak the JQL to show issues from both current and future sprints together:

project = ABC AND (Sprint in openSprints() OR Sprint in futureSprint())

This way you would see both sets of issues together.  Admittedly, this might be more data than you want to see in this dashboard at once.

Is viewing the backlog, on the Agile Board insufficient to see these current and upcoming issue?   I'm wondering if using a separate Dashboard might not be necessary here.  Instead maybe you can use some quick filters on that backlog in order to quickly hide issues that might be in a 'in progress' or 'done' status.  That way you could quickly see only issues that are in a 'to do' status and still view them by their current or planned sprints.   Maybe I'm way off base from what you are looking for here, but please let me know either way.

Joshua Balsillie July 27, 2018

@Andrew

project = ABC AND (Sprint in openSprints() OR Sprint in futureSprint())

I thought about something similar to this, but could not find any documentation to suggest that openSprints() or futureSprints() returns any sort of boolean / "falsey" value. Which would have been useful.

I am under the impressions "Sprint in [X]" returns an object. Whether that object is empty or not does not matter, you will see either: values or no values. I think being able to detect null values would be useful, but could not figure out how to do this.

Unfortunately your suggestion doesn't suit my use case, without some SQL workaround. (Of which I don't have the interest in developing)

It's not ideal because futureSprints isn't all that useful information alone. Unless due dates are diligently updated at the start of a sprint, I don't see many beneficial use cases for it.

Like Sri Chavali likes this
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
July 30, 2018

Jira's JQL is specifically designed to return Jira issues.  And it does that.  When you use the parameters such as 'sprint in openSprints()' this returns all issues in Jira whose sprint field value matches a sprint that is currently open.

I can understand why it would be helpful to have this conditional syntax, but this is not something that Jira has built within itself.

Like Sri Chavali likes this
Joshua Balsillie July 31, 2018

@Andy Heinzer

Thanks, I definitely understand the reasoning, but think there is some opportunity for this type of functionality.

For anyone interested in having this feature, I've opened up a "suggestion" story with Atlassian:

https://jira.atlassian.com/browse/JRACLOUD-69924

Follow the link and "vote" if you would like to see this feature.

Like Sri Chavali likes this

Suggest an answer

Log in or Sign up to answer