JIRA(JQL) filter related

[Chandrashekhar Kavi]

I am not JIRA admin, I can see different columns with same name in exported xls from jira issues, now when I am trying to filter that sprint is empty it's not filtering all the records, how to get relevant result?

[Chandrashekhar Kavi]

[Boyan Iliev]

Ok, so I'm mostly on the same page. Please do try replacing "Sprint is Empty" with "(Sprint is Empty or Sprint not in (OpenSprints(),futuresprints()))" and let me know if you get what you are looking for.

[Boyan Iliev]

Hi @Chandrashekhar Kavi  , can you provide some details:

1. What are you trying to achieve in general? Get a list with all tickets in a project that do not have assigned sprint?
2. Are you trying to filter "sprint is EMPTY" in JQL or in the exported data?
3. What kind of tickets you get, that you expect to be filtered out? Were they in a sprint that was closed in the past?
4. Can you give an example of the "same name" columns? Is it something like "Comments 1", "Comments 2", "Comments 3", etc?

[Chandrashekhar Kavi]

Thanks Boyan, It's now done, I am not able to explain how it's done due to it's showing the above message while replying!

JQL, I used is as below.

[Chandrashekhar Kavi]

Sorry Boyan, I am still facing the issue as when exported rows using the previous JQL, it's still showing some issues which are having values in sprint column in xlsx!

Please refer images attached in this message.

[Chandrashekhar Kavi]

Please also refer the image for more reference

[Boyan Iliev]

I'm still a bit unsure of what you try to do, but here is my guess - you do not want to see the tickets in the screenshot above as you consider them not in a sprint. Yet these tickets were in sprints that are now closed. This is what the "+4" shows next to "None" - 4 closed sprints.

What you should do is filter out Empty(as you did) OR if sprint is closed, while at the same time the not placed in any open or future sprints. Try replacing "Sprint is Empty" with something like this "(Sprint is Empty or Sprint not in (OpenSprints(),futuresprints()))".

This is how JIRA treats sprints. A ticket can be:

1. In no sprint - empty
   or
2. in closed, open or future sprint

You cannot have ticket with empty sprint and closed sprint. Although visually it might look like so - the "None +4" representation

[Chandrashekhar Kavi]

Hello,

My purpose to get list of issues which are having statuses (todo, new, archived, dolater) and sprint is empty and assignee is not empty. But as I shared image earlier it's showing issues with something like +4, which I don't want in the list.

Hope I am clear.

Thanks

[Boyan Iliev]

OK, it seems like we are talking for the same thing.

Please try replacing "Sprint is Empty" with "(Sprint is Empty or Sprint not in (OpenSprints(),futuresprints()))" and let me know if it works.

[Chandrashekhar Kavi]

I tried with the above JQL, but it not worked for me! it's showing more records now where result is expected to no rows.

[Boyan Iliev]

It should be

... or Sprint not in ...

you added just 

... or Sprint in...

[Chandrashekhar Kavi]

Hi, still there is showing result with sprint!

[Chandrashekhar Kavi]

When checked in excel, it's showing records as below, It should be displayed a single record, but it's showing 8 records!

[Boyan Iliev]

Hi, I have misunderstood you. If you are still working on this, can you try one more thing? Replace everything I suggested

(Sprint is Empty or Sprint not in (OpenSprints(),futuresprints()))

with

Sprint in (EMPTY)

[Chandrashekhar Kavi]

[Boyan Iliev]

This is strange, do you know what the "Central Delivery Projects" filleter does? Is it possible that it adds these extra tickets?

I'm running out of ideas. Can you try only:

Sprint in (Empty) and Project = "Payroll Module"?

[Chandrashekhar Kavi]

No that has not "OR" condition used in filter="Central Delivery PRojects" but only saved filter for different 15 or more projects for my comfort. Because some of the projects concern with Department. If you want I can share without project or any filter in JQL.

[Boyan Iliev]

No need to share the filter if there is no "OR" Another guess is that sprint and the is/in keywords are somehow acting in a way I do not understand.

Did you try: Sprint in (Empty) and Project = "Payroll Module"

can you also try: Sprint = empty  and Project = "Payroll Module"

[Chandrashekhar Kavi]

Tried all the way, no result change!

[Chandrashekhar Kavi]

[Boyan Iliev]

Well I have no explanation. I'll try to dig around, but in the mean time, my best suggestion is to filter it in excel itself.

1. Find all "Sprint" columns
2. Delete all but the first one
3. Filter the first by value - if it has value, row it should be deleted
4. All remaining rows should have no sprint

I'm not sure what amount of data you are dealing with, if this is a recurring extract or a one time thing, so based on the above you might need some automation.

[Chandrashekhar Kavi]

I had already completed for one time with excel, but it will not be helpful to me as I was going to set automation condition "sprint is empty and assignee is not empty", action "Replace assignee with empty". This action can't be done using excel, so it's not helpful for me accordingly. Anyway you tried the best and hope you would find the solution on this.

Thanks Boyan.

[Boyan Iliev]

After some digging, I found some contradicting sources...In the instance I'm using "Sprint is Empty" excludes tickets in closed sprints - what you wanted to achieve. Exception are few corner cases with sub-tasks tickets. According some documentation, JIRA treats closed sprints as "empty" ones, so actually your behavior sounds like the the correct one.

Here are few more ideas you can try out, but at this point, I'm shooting in the dark:

1. Are any/all of the tickets with "none +X" of a "special" type - epic or subtasks? If yes, you can probably filter them out using the "type not in (epic, "sub-task")".
   • Subtasks inherit sprint from parent, so no need to worry about them,
   • Epics are usually small amount, so can be managed manually.
2. I would assume that all tickets that are not in closed, open or future sprint are with no sprint. in my JIRA instance such filter returns 0 results. Considering the fact that my instance differs from what you see, it might be worth checking what you will get with a "sprint NOT IN (openSprints(), futureSprints(), closedSprints())" JQL.
3. There is a free addin to pull JIRA tickets into excel/google sheets. Use it to pull the data there, do some magic in the excel (filter by the first "Sprint" column etc) and use excel to manually process the tickets once every few days/sprint end
   • https://marketplace.atlassian.com/apps/1221301/jira-cloud-for-excel-official?tab=overview&hosting=cloud
   • https://marketplace.atlassian.com/apps/1220382/jira-cloud-for-google-sheets-official?tab=overview&hosting=cloud
4. As you want to leverage this in automation, you can explore an automation with smart values(https://support.atlassian.com/cloud-automation/docs/smart-values-in-jira-automation/) instead of JQL. I'm no expert there, but on a high-level:
   1. Trigger automation on "Field value changed" and pick the sprint field
   2. Add an IF block with "{{smart values}} condition"
   3. Use {{issue.sprint}} to get a list of all sprints of the ticket and check fi the list is empty "list.isEmpty" -> more info here https://support.atlassian.com/cloud-automation/docs/jira-smart-values-lists/ . I have not tested it, but it sounds like a very basic function, not dependent on sprint status.
      • If {{issue.sprint}} does not work, use {{issue.fields.customfield_XXXXX}}
      • the sprint field ID (XXXXX) can be found by an admin, or by adding it to your search results columns and expecting the page source (there are few tutorials on how to get the custom field ID if you google for them)
   4. After that it should be easy - get a second IF block to check assignee If assignee is NOT empty and list of sprint is empty - unassign
   5. This does take care of any new tickets, but leave the question on how to process the existing ones.
      • If they are not too much, using your excel approach - filter the ones with no sprint and assignee, and create a JQL by "key in (XXX,YYY,etc)". Bulk edit them to unassign.
      • if they are a lot, do a "dummy" bulk change, so they trigger the rule above. For example - add a dummy label and remove it after that.