JQL involving multiple projects

[Shir_Glozman]

Hello,

I'm trying to write a JQL where I want to limit the results by one of the fields in another project;

Some more details - here's my query (that doesnt work):

Filter = "Sales EPICs_Active" and "External Project" in (project = PROJECT_ID AND status in (optimization, "BAU Rollout", "Project Review", "Project Completed", Archived))

"Sales EPICs_Active" - that's a filter that gets a list of EPICs.

Each EPIC has an 'External Project' field, which is basically a closed list of another Jira project. each project in the External Project has a status, and I would like to limit the Sales EPICs results so that I'll get only EPICs that are related to projects that are in a certain status.

I hope this makes sense, please advise how this can be done,

many thanks!

Shir

[Thomas Bowskill · Accepted solution]

Hi @Shir_Glozman 

I'm not fully following you, but are you asking if it possible to take the results of one JQL query and use it to join on (/ reference) another query and filter the results down (opposed to merging them)? If that is the question, then no unfortunately JQL doesn't have the same functionality as SQL -- there is a request raised for this

If that is not what you are asking, could you clarify your question?

p.s. if this helps we generally use EazyBI for a lot of our more complex reporting, as the only other option we saw for doing these join-based queries would have been some form of automated scriptrunner that would update fields on the child stories based on the parent Epic (e.g. so that if your Epic is transitioned to another state - like "Project Review" to "Project Completed" - then this status will be mapped to all the Child Stories, which them means your query doesn't have to join onto another set of results, as you've copied the data to the Stories). Generally I like avoiding solutions like this as eventually they break, or you change something and you've forgotten they were in place to begin with.

Another thought: have you looked at the Portfolio for JIRA add-on? It's a project management overlay on JIRA, and it seems that what you are trying to do is convey statuses and progress on Projects -- which this does quite well.

[Shir_Glozman]

Thanks Thomas,

That was exactly what I was looking for - the ability to 'Join' 2 filters results based on a common field (on a more simple cases I usually use 'Filter' but it won't work here as it's a different case).

These days we are examining a few Program/ Portfolio tools, and Portfolio for Jira is among them.

Thanks again,

Shir