JQL for epics with empty CM Change Types that are under Initiatives marked as a Work Type of Change

[Autumn Salinas]

I need to write a query that will show all epics with an empty CM Change Type that are under Initiatives that have a Work Type of Change. I've been able to write a query to show all initiatives with a work type of change and a query to show epics with empty change types, but can't figure out how to combine the two for the desired results.

Someone told me I could use issuekey in childIssuesOf("") but I'd have to manually enter the Jira IDs that the other query produced. My thought would be a nested function, but I know JQL does not have that capability.

Is there a workaround for this? Any tips would be appreciated.

Right now, my company is in data center but we are migrating to the cloud some time in January. Unsure if that's relevant info, but including just in case. 

[Autumn Salinas]

I did try the answer from Bhushan but kept getting an error about "workType" being invalid. I'm assuming it had something to do with how my employer configured our custom fields. I did end up reaching out to an internal Jira team. Unfortunately, we found it easiest to write a query to pull all initiatives with a change type of Change and then use those results in "issuekey in childIssuesOf()" It was a lot of manual work but it got the results we needed. I imagine "issueType = Epic AND parentLink in (KEY-1, KEY-2, ...)" would have saved me a bit of typing, but not a lot.

Thank you both so much for your input and guidance. I definitely learned some new things and because of that your answers have been invaluable

[Hannes Obweger - JXL for Jira]

Hi @Autumn Salinas

welcome to the community!

Unfortunately, this is trickier than one might think; as a hierarchical query, it would really require some kind of join or subquery, which isn't available in plain Jira/JQL.

A few directions forward:

• If it's a one-off thing, you should be able to first query the relevant initiatives, and then use the keys of these initiatives in a second query, in an "issueType = Epic AND parentLink in (KEY-1, KEY-2, ...)" (etc. etc.) clause. This syntax assumes that you've modelled your initiative/epic hierarchy in Advanced Roadmaps - is this correct?

If you want to run your search dynamically, without manually stitching two queries together, you'll need extra tooling:

• You might be able to use Jira Automation to "propagate" initiative information down to the initiative's children, and then use the respective field(s) on the children to include them into your filter. Obviously, this will add a fair bit of complexity to your system.
• There's different apps from the Atlassian Marketplace that can help with that. First, there's a number of apps that extend JQL by additional functions, including hierarchy-related functions. The "issueFunction in" syntax that is mentioned in another answer actually comes from the ScriptRunner app, but there are other options too.
• Alternatively, you could try one of the more hierarchy-focused apps from the Marketplace. These apps typically have their own ways of figuring out parent/child relationships between issues, and provide more powerful ways of searching through issue hierarchies. I myself work on such an app, in which your use case would be easy to solve - I'll provide more details below.

Hope this helps,

Best,

Hannes

[Hannes Obweger - JXL for Jira]

Just to expand on the last point, this is how this would look in the app that my team and I are working on, JXL for Jira. Put simply, you'd create a sheet with all issues that are potentially relevant to you, model your initiative/epic-based issue hierarchy (that's just a couple of clicks), and then use JXL filtering capabilities to narrow down to the issues that you care about:

Once you have your list of issues, you can work on these directly in JXL (much like you'd do in e.g. Excel or Google Sheets), trigger various operations in Jira, or export them for further processing.

Any questions just let me know!

[BHUSHAN PATIL]

Hi @Autumn Salinas ,

Combining these conditions in JQL can be a bit tricky, especially when you're dealing with multiple layers of issue hierarchy and conditions. Unfortunately, JQL doesn't have a direct nested function capability for such complex queries. However, there might be a workaround.

Assuming your Initiatives and Epics are linked via issue links, here's an approach using the issue in and linkedIssue functions:

issueFunction in linkedIssuesOf("workType = Change and type = Initiative") AND issueType = Epic AND "CM Change Type" is EMPTY

This query attempts to find all Epics that are linked to Initiatives of work type "Change" and have an empty "CM Change Type."

• Replace "CM Change Type" with the actual name of your field if it's different.
• Ensure the field names and conditions match your Jira configuration.

Also, if you feel my input help you please accept the answer.

Regards,
Bhushan