Looking for a "not only" function in JQL query

[Katharina Ickert]

Hello everyone,

I want to filter projects by values in Jira with the advanced search. 

There are many values and some of them are combined in one project. I would like the programm to show me every project that doesn't contain one specific value only. So in combination with another values it would be fine, but not if it was the only value in the projekt in this field.

For example: 

Field would be "Colors" and the values are "green", "blue", "red" and "yellow"

Jira should show me all projects that contain the combination of the colors or a color allone in a project exept the color red. So if there is a project which is assigned to red only, Jira should show me all projects exept this one with red. But red in combination with another color is fine. 

[Aron Gombas _Midori_ · Accepted solution]

You can't search for projects using JQL, period. JQL searches only for issues.

For this type of use case, I think you should:

1. Either write an SQL query against the database.
2. Use a "reporting" type app which counts the number of issues with the different "Colors" value per project. If a project has issues with the same "Colors", then you have it!

For approach 2:

1. Export all your issues to Excel with this app
   1. Make sure that you export the "Project", the "Issue key" and "Colors" columns
2. Create a pivot table
   1. Group by project
   2. Count the number of issues having the same "Colors" value
3. Check the pivot table where the project has only one record (meaning that all issues have the same "Colors"

(Dicl. this app is developed by my team. It is paid, supported and solves your use case.)