How can I filter out tickets with status = Canceled

[Gavriel Fleischer]

I am not able to filter out (exclude) the tickets with status: CANCELED .

I have a filter, where I am able to filter out certain statuses using:

assignee = currentUser() AND status NOT IN(Closed, Done, Backlog, "To Do", Cancelled, Canceled, PROD, "on hold", Pending, "Pending Devops", "pending DB team") AND "Epic Link" = EMPTY AND type != Epic AND parent is EMPTY AND (labels != waiting OR labels is EMPTY) AND project != "Server Design Board" ORDER BY priority DESC, rank ASC

But I still see some tickets in the results with status: CANCELED

Any idea how can I filter them out?

(No, adding "AND status != CANCELED" doesn't help)

[Vickey Palzor Lepcha]

Can you share the first half of the jql too ? Ideally , I can see that your JQL should work unless it is picking issues from multiple projects ?

[Gavriel Fleischer]

Added the full query. Indeed it is picking up tickets from multiple projects. That is the reason why there are som many excluded statuses, because each team sets up their project differently

[Kai Becker]

Hi @Gavriel Fleischer 

thanks for your question. Most of the time there is an logical issue with the JQL. Would you mind pasting the whole JQL query, so we can have a closer look?

One thing to add right now: There is an easier way in excluding status. You can put them all together like this:

status NOT IN (Closed, Done, Backlog, ...)

[Gavriel Fleischer]

Thanks, Kai,

1st of all I modified it now to use the NOT IN, thanks for that tip. Here's the query:

assignee = currentUser() AND status NOT IN(Closed, Done, Backlog, "To Do", Cancelled, Canceled, PROD, "on hold", Pending, "Pending Devops", "pending DB team") AND "Epic Link" = EMPTY AND type != Epic AND parent is EMPTY AND (labels != waiting OR labels is EMPTY) AND project != "Server Design Board" ORDER BY priority DESC, rank ASC

As you might guess there are multiple projects in our company, and each team sets up their project differently, thus so many statuses (including typos). My problem is that the above query still returns tickets with status: "CANCELED"

[Kai Becker]

Hi @Gavriel Fleischer 
thanks for the update. Just to make sure. Are these Company Managed Projects or Team Managed?

The next step I would take: start the JQL just with the user and status part. Check if there are still issues with CANCELED and then go on and add each part next to identify which part brings them in.

[Gavriel Fleischer]

I don't know if they are company or team managed (or what are the differences between them or how I can tell them apart)

I also don't really understand your recommendation regarding how to debug it. I mean if I understand what you mean then that's the way this query was built. I had only the user, then filtered out more and more things as new tickets showed up that I didn't want to see in the filter. And it kind of works. It does filter out lots of tickets in different projects with all those statuses.

Can it be that some projects defined the status as Canceled, others as canceled or CANCELED and the filter is case sensitive, but the way it is displayed I only see them capitalized?

Though even then when I type "AND status != C..." I see the UI gives me suggestions, so maybe there is the bug, that I am supposed to see another one, but either in the suggestion or in the filter somewhere the case sensitivity is lost? 

[Kai Becker]

Hi @Gavriel Fleischer 

thats exactly why I was asking about the project type. If there are team managed projects, they do use different status encapsulated from the rest of the Jira config.

The diffenrece between these to types can be found here:
https://support.atlassian.com/jira-software-cloud/docs/what-are-team-managed-and-company-managed-projects/

The easiest way to identify is going to the projects and check the lower left corner:

If you are a Jira admin you could also take a look at https://<your-site>.atlassian.net/secure/admin/ViewStatuses.jspa and check if there are status you want to exclude. But be aware, that statuses from team managed projects will not show up here.

Another option to try would be to reverse the status part in your JQL to only include the statuses you want. But this depends on the amount of statuses.
I'm still not quite sure what you are trying to achieve, as there are statuses from every category (Todo, In progress, Done) filtered out.

One last thing: What do you mean by: "Though even then when I type "AND status != C..." I see the UI gives me suggestions". Are there suggestions? Can you select them? Does an error show up?

[Gavriel Fleischer]

Ok, I checked, and the MAPIS (the one that I still see the "Canceled" status tickets from) project is a company managed project.

When I edit the filter and I start to type "AND status != C" then it gives me the "Canceled" as an option, but I already have that in my query, so adding it again doesn't help, does it?

The goal of the filter is to give me the tickets that I am "working on". Since we sometimes get to work on tickets from new projects I started with the obvious: status != Closed, and gradually added other statuses that are not relevant. This is how I got to this point. I don't think it is maintainable to reverse it, because a) I would have even more statuses, b) I don't know what statuses in any new project (or even an existing project's workflow can be changed) can be present. That's why I used the black-listing tactic.

Besides if what you're saying is true, that if 2 projects have the same status, then even with white-listing I would have the same problem, wouldn't I? And actually it would be even worse, because then tickets would be invisible for me.

I also can't go to all kinds of teams and ask them to change their workflow because "Jira has a bug, so every status of every project has to be distinct..."

So maybe you could fix this bug!?

[Kai Becker]

When I edit the filter and I start to type "AND status != C" then it gives me the "Canceled" as an option, but I already have that in my query, so adding it again doesn't help, does it?

As long as you are able to edit it, I would suggest to do so. These statuses might have different IDs in the background, so this is why they appear twice or even more often.

[Gavriel Fleischer]

No, it always gives all the statuses. It's not that clever to filter out the ones I already have in my query...

assignee = currentUser() AND status NOT IN(Closed, Done, Backlog, "To Do", Cancelled, Canceled, Canceled, Canceled, PROD, "on hold", Pending, "Pending Devops", "pending DB team") AND "Epic Link" = EMPTY AND type != Epic AND parent is EMPTY AND (labels != waiting OR labels is EMPTY) AND project != "Server Design Board" ORDER BY priority DESC, rank ASC

Still getting the same results :(