Query to find all children of an initiative, when an initiative component is X

[Sheri Widler]

We are using jira advanced roadmap and we have priorities and initiatives in one project such as: 
PRIORITY-1 (Top level)  (We only have 5 of these) 
INITIATIVE-1 (CHILD OF PRIORITY)

we then have epics in our working project and they are children of the Initiative.  

Initiative has a component of "executive priority".

I want to create an advanced roadmap including all Initiatives where component = executive priority and all portfolio children under the initiative.  
I was trying this to use the least number of clauses in the query and pulling in the top level and portfolio children: 
issuekey in portfolioChildIssuesOf("PRIORITY-1") or issuekey in portfolioChildIssuesOf("PRIORITY-2") or issuekey in portfolioChildIssuesOf("PRIORITY-3") or issuekey in portfolioChildIssuesOf("PRIORITY-4") or issuekey in portfolioChildIssuesOf("PRIORITY-5") and component = "Executive Initiative "

But I don't get the right results because I think the component is on the INITIATIVE LEVEL. So then tried to nest the criteria so I wouldnt have to query exact issuekey 

issuekey in portfolioChildIssuesOf("type = Initiative and component in 'Executive Initiative ' ")

This also does not work. 
Help. 

Ideally if I had these two initiatives and portfolio children, I would want only initiative 2 and children

PRIORITY-1 (Top level) 
INITIATIVE-1 (CHILD OF PRIORITY) (Executive Priority = No)
EPIC-1 
STORY-1, STORY-2, STORY-3

PRIORITY-2 (Top level) 
INITIATIVE-2 (CHILD OF PRIORITY) (Executive Priority = Yes)
EPIC-2
STORY-4, STORY-5, STORY-6

[Sheri Widler · Accepted solution]

We actually added a table in assets that is the initiative and traverses down the structure to easily use one field to query everything for a plan.  We also have another table in assets of initiative type and can query initiative by business area.  

[Hannes Obweger - JXL for Jira]

Hi @Sheri Widler,

when you say you want to see "all children", do I understand correctly that you'd like to see the entire "children hierarchy" below your initiatives, i.e., the epics, stories, and potentially sub-tasks?

If that's the case: This is a tricky one; as a "hierarchical query", it would really require some kind of "join" or "subquery", which isn't available in "plain" JQL. To the best of my knowledge, you'll need extra tooling for this.

A few directions forward:

• You might be able to use Jira Automation to "propagate" an initiative's "Executive Priority" field down to it's children, grand-children, etc., and then use the respective field on the children to include them into your filter. I'm not an expert in Automation so I can't really advice on the exact automation rule.
• 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. I've used JQL Search Extensions a few times and it works great.
• 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 defining and searching through issue hierarchies. I myself work on such an app, in which your use case should 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/story 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,

Best,

Hannes

[Sean Blezard]

This was a trivial problem in Jira Server with plugins like Scriptrunner. Annoyingly Jira Cloud doesn’t support portfolioChildrenOf(“query’), which means Scriptrunner doesn’t offer it either.

Structure can handle this but it’s not compatible with Advanced Roadmaps or other OOTB Jira features. You’d have to use their GANTT add-on to visualise as a roadmap.

Atlassian really screwed up the transition to cloud on features like this one (getting children of parents)

[McKesey, David]

Agreed.