Find the list of stories with epic with a parent link not in project A

[Marc-O]

I have multiple projects.

In Project A, I have a set of stories and epics.

I have initiatives in projects B, C, and D. Some of the epics are linked to initiatives in B, C, and D.

How can I find the list of stories for which the epic in the epic link does not have a parent in Project B?

I have access to ScriptRunner for Jira Data Center plugin.

I cannot install additional plugins. I cannot create custom fields on many of those projects.

 I have tried 

issuetype = Story AND project = A AND "Epic Link" not in (issueFunction in portfolioChildrenOf("project != B"))

But I got the following message:

No issues have a parent epic with key or name 'issueFunction in portfolioChildrenOf("project != B")'

[Stepan Kholodov _Tempo_]

Hello @Marc-O 

If the query itself is executed fast in Jira search but the 'We couldn't load the structure' banner appears when you use the query in a structure, it might indicate that there is a performance problem somewhere. When it comes to loading the content of structures, many different aspects can affect the process, both internal and external.

If you need assistance with it, please submit a support request at our portal and we'll look into it.

Best regards,
Stepan
Tempo (the Structure app vendor)

[Marc-O]

Thanks for the offer. 
Unfortunately, I cannot access the information required at the service desk (SEN): there is no such number when I click on info at the bottom right of the Structure page.

Also, it is an enterprise DC instance, so I won't be able to share many details other than the simplified information I shared above (I'm not sure what you would require to help).

[Mark Segall]

Hi @Marc-O and welcome to the community!

I think the Epic Link portion is throwing things off.  I think you simply need this:

issuetype = Story AND project = A AND issueFunction in portfolioChildrenOf("project != B")

By explicitly stating you want project A stories that are in the hierarchy of initiatives not in project B, you shouldn't need the epic link part of it.

[Marc-O]

Thanks.

That's indeed simpler. I wanted to check simultaneously that the Story was linked to an Initiative through an Epic.

With the above query, I can still have Stories linked to Initiative as a parent.

But I guess I can have two different checks.

Your query above works!

I have a side issue when I use it though.

The end use case is to use this filter (including others) in a Jira structure to audit the quality of the data we have.

When I query Jira using 

issuetype = Story AND project = A AND issueFunction in portfolioChildrenOf("project != B")

It works, and the query is fast, even without any additional filters.

But when I use it in a Jira Structure, the query takes too much time, and the Structure generation fails.

I tried to use many filters in the query to reduce its potential complexity but without success.

Is the portfolioChildrenOf a potential reason within a structure? 

[Marc-O]

@Mark Segall, it did not work.
The proposed query gave me results, which I initially was the answer to.

But the results were false positives: stories that had an initiative as parent (and thus did not have an epic, since a story can't have a parent and an epic simultaneously).

According to the ScriptRunner documentation, portfolioChildrenOf "does not traverse the link from Epic to Story".

I tried another approach, but it does not seem to work as I have far too many initiatives not in Project B

issuetype = Story AND project = A AND issueFunction in issuesInEpics('issueFunction in portfolioChildrenOf("project != B")')

Simply fails because there seem to be tens of thousands of tickets not in project B, even if I filter them on the issuetype = Initiative...
I will need to find another strategy.

[Mark Segall]

Hmm... I don't have a data center environment in front of me and I know that cloud is a little different, but in cloud when I execute the equivalent query, it traverses the entire hierarchy. To be clear, you're saying that portfolioChildrenOf() only returns direct descendants of the initiative?

If yes, your followup query leveraging issuesInEpics() is correct.  However, there are definitely limitations.  Perhaps you can further refine the query to only return children where the Initiatives and Epics are unresolved:

issuetype = Story AND project = A AND issueFunction in issuesInEpics('issueFunction in portfolioChildrenOf("project != B AND resolved IS EMPTY") AND resolved IS EMPTY')

[Marc-O]

Yep, the adaptatvist doc for DC is in line with what I can see on my server: https://docs.adaptavist.com/sr4js/7.9.0/features/jql-functions/included-jql-functions/portfolio

I think I now know why I could not find results. And yes, I must find an appropriate filter strategy to decrease the number.
Thanks a lot!

[Marc-O]

I actually pulled the advice from the ScriptRunner doc even more and got a working solution!

I created the following filters:

filter "list of initiatives not in project B"

project != B AND issuetype = Initiative AND "Target end" >"2024/1/1" AND statusCategory != "To Do" AND status not in (Canceled, Prioritization, Scoping, Rejected, "On Hold") AND "Parent Link" is not EMPTY [AND etc., so to make the list more manageable]

filter "list of epics under initiatives not in project B"

issueFunction in portfolioChildrenOf('filter="list of initiatives not in project B"') AND issuetype = Epic

Final query

issueFunction in issuesInEpics('filter="list of epics under initiatives not in project B"') AND issuetype = Story AND project = A

Thanks a lot for helping me out!

The answer was in the doc (again!)

[Mark Segall]

Good call out of merging filters together and thank you for sharing the results.  This helps others who may be experiencing the same issue.