Filter issues within an Advanced Roadmap vs Not within an Advanced Roadmap

[Evan Giordanella]

I have the following hierarchy within advanced roadmaps

Theme -> Initiative -> Epic -> Stories, Tasks. 

I am looking to setup a filter/dashboard to do the following:
All In Progress issues that are within the above hierarchy
and
All In Progress issues that are NOT within the above hierarchy

I use EazyBI for a lot of more complex reports, but even there I am struggling. I have the hierarchy configured in EasyBIs advanced settings, and I can see advanced roadmap dimensions but I don't know where to go from there.  It's almost as if I want 2 types of report specific measures (on roadmap, not on roadmap)

Any help or suggestions on how to achieve this? 

[Gerda Grantiņa · Accepted solution]

Hi @Evan Giordanella

When you have imported the Advanced Roadmap hierarchy in your eazyBI account, eazyBI creates a hierarchy in the Issue dimension based on the Advanced Roadmap setup and also creates additional Advanced Roadmap dimensions. 
The issues that are not in the hierarchy are under the member "(none)," and this can be used to create a report to see all "In Progress" issues that are within the Advanced Roadmap hierarchy and what are NOT within the Advanced Roadmap hierarchy.
I am using the Issue dimension in Rows and selecting AR hierarchy, and I added the "Advanced Rodamp Program" dimension (you will have "Advanced Roadmap Theme") in Pages.
In the "Advanced Rodamp Program," I created two calculated members:
1. "Within Advanced Roadmap":

Aggregate(
Except(
[Advanced Roadmaps Program].[Advanced Roadmaps Program].Members,
{[Advanced Roadmaps Program].[(none)]}
)
)

2. "Outside Advanced Roadmap"

[Advanced Roadmaps Program].[(none)]

Additionally, I am adding a "Status" dimension in Pages to filter issues by status "In Progress"

And now, the report looks like this, and I can filter issues that are within the hierarchy or not in it:

best,
Gerda // suport@eazyBI.com

[Evan Giordanella]

Thank you! This was so much simpler than I was trying to do. This works perfectly!

[Mark Segall]

Hi @Evan Giordanella - Are you basically looking for orphan initiatives/epics vs those with a parent Theme/Initiative respectively?

If so, you could just do a filter like this:

Orphans

IssueType IN (Initiative, Epic) AND "parent link" IS EMPTY

Not Orphans

IssueType IN (Initiative, Epic) AND "parent link" IS NOT EMPTY

If you're looking for something more specific like identifying whether an epic has been mistakenly linked to a Theme, it's unfortunately not straightforward.  You have to be very explicit with any kind of hierarchical JQL.  For example...

issueType = Epic AND issuekey in childIssuesOf("THEME-001")

I hope this helps.

[Evan Giordanella]

This gets me closer! I'm curious to all descendants of both scenarios. 

[Mark Segall]

The descendants of anything below the epic shouldn't matter because that's established in the issue hierarchy.  In fact, the parent link field should be flat out hidden from all issue type screens below the epic to make sure nobody is inadvertently linking say, a story to a theme.

Getting a list of say, all initiatives and their descendants is just not easy with JQL.  Because of the constraint of the childIssuesOf() function that you have to explicitly call out the key, there's no way to perform a query for say, give me all Initiatives and their children.  For any type of analysis you want to do above the Epic, your best bet is to do it from AR.

[Evan Giordanella]

Have you done something like that in EazyBI?

[Mark Segall]

Can't say that I have, but I found this link if it helps:

https://community.eazybi.com/t/how-eazybi-supporting-portfolio/2692/2

[Alex hensen]

I use EazyBI for a lot of more complex reports, but even there I am struggling. I have the hierarchy configured in EasyBIs advanced settings, and I can see advanced roadmap dimensions but I don't know where to go from there.