JQL to return parents of parents until initative is reached if it exists

[James Booth]

I have a Project (P1) that uses advanced roadmaps and initiatives. We have another project (P2) that I want query tickets from. I'm querying sub-tasks from the P2 project on my roadmap, but they all show as "issues without parent".

I want to show the complete hierarchy of issue types on my roadmap - parents of parents, etc.

I need to find the sub-tasks in the P2 project and also their parents (stories) and their parents (epics) and their parents (initiatives) so that on my roadmap I won't have issues without parents and I can see the entire hierarchy of tickets that I'm interested in.

Is this possible with JQL, Enhanced Search or ScriptRunner?

[Florian]

Hi James,

unfortunately this is not possible out of the box. JQL is not as mighty as SQL. And even with SQL this something for advanced users and not every database supports this (some do with CTE). 

There are several plugins that allow n-Level trees. Take a look at Big Gantt or Structure at the marketplace. 

[James Booth]

I had my doubts about "out of the box", but do you know if ScriptRunner or Enhanced Search would work? I'll take a look at the ones you mentioned, as well.

[Florian]

Sorry I don’t know. ScriptRunner might allow you to recursively iterate over multiple “generations“ but I doubt it allows you to embed it into JQL. But I have no experience with ScriptRunner and I don’t know what the Jira API provides for JQL. Although I know it is one of the most versatile plugins for users with programming skills. So probably it worth to spend some time in this area.
We decided not to use ScriptRunner because it allows to over customize Jira and we want to stick to the standard or at least we want support when new releases are being brought to the market. That’s a very conservative point of view but we learned our lessons from other systems where we tried to satisfy all our users wishes. Sooner or later it will hit you like a boomerang. 

About Enhanced Search I can’t tell anything. 

[James Booth]

I have to go through a procurement process to get another plugin installed, so I have to use what I have in front of me. Using Enhanced Search, subset of ScriptRunner, I discovered that I can can get almost everything I want by creating multiple filters.

Filter 1 - "Get the Stories and SubTasks" - returns the stories and subtasks

Filter 2 - "Get the Epics of Filter 1 Or Filter 1" - returns the epics, stories and subtasks

Filter 3 - I want to use "Get the Parents of Filter 2 Or Filter 2" to get the initiatives, but I don't see any way to get the initiative of an Epic.

I went through tons of documentation but can't seem to find how to get the initiatives of epics. Any ideas there?

[Dave Rosenlund _Trundl_]

Congrats!  But this is where everyone hits a wall, James. The Epic >Story > Sub-task hierarchy/relationship is "special."

Jira, and many Jira apps, know exactly what to do with those issue types.

Beyond that OOB hierarchy, all bets are off unless you are using an app that knows what how to extend hierarchies and add issue types in a sensible way above the Epic (or below a Sub-task, or in between those 3).

Your org. has to decide: Dogma (Epic > Story > Sub-task), or flexibility -- with an app).  

There are tons of threads on the community about this -- you're not the first to be frustrated by it. And this is one of the reasons why Advance Roadmaps, Structure, and Big Picture were created.  

(I'm not making this up -- do a search here ;)).

This article may be of interest to you.

Hope this helps,

-dave [ALM Works]

[Mateusz Sołowianiuk]

Hi @James Booth 

As @Dave Rosenlund _Trundl_ mentioned, BigPicture allows you to do that rather quickly. Additionally, it will allow you to present your project plan in several ways, useful for different purposes (Portfolio Overview, Scope, Gantt chart).

Please feel free to try our product in our Demo app (no installation required) - it will give you a good overview of the product and its capabilities, enabling you to find out if this is something you are looking for.

I hope that you find it useful.

Best regards,

Mateusz Sołowianiuk

SoftwarePlant Support

[Dave Rosenlund _Trundl_]

Hi, @James Booth. Structure for Jira (and BigPictiure, too) was built to enable you to easily do this very thing because, well, there is no easy way to do what you describe with JQL and/or scripting. You'd basically have to reinvent these Jira apps to do it. ;) 

I work for the company that created Structure. You can easily install a trial version in your instance to learn how easy it is to build the sort of hierarchical view you describe -- the way you described it -- with Structure The method described here should do the trick (except that you'll be starting from the sub-task level).

Hope this helps.

-dave [ALM Works]