JQL Query for Epics with Stories in Active Development Status

[David Young]

I am trying to generate a query to list Epics with linked stories in an active search state.  I can easily search for stories with Epic Links in an active status via

"Epic Link" is not Empty AND Status in (Working, "Tech Review", "Integration QA", "Ready for Demo", "Ready in Master QA", "Master QA")

This shows me the individual stories, which is great, but it is too granular.  I am looking to go up a level where the list that is generated is a list of Epics, not list of stories.  Basically invert this query so that the results are just Epic ID ## generated by the fact that there are stories with EpicLinks in one of the status I've listed.  Any assistance would be appreciated.  Thank you very much.

[David Young]

Alright, I talked to Atlassian reps while at a conference last week, and I have a partial solution, which works in part but for some reason I can't expand upon.  Here is the query that is working, partially:

project in (List of Jira Projects) AND issuetype = Epic AND resolution is EMPTY AND issueFunction in linkedIssuesOf("issuetype in (Enhancement, Bug, Research, Refactor, Story, Task) AND status = Working", "has Epic")

This query brings back results, however, the results bring back only those Epics with Epic Issue Links that are in the status of "Working".  I attempted to expand this query to include more status in the workflow, but since the statuses are multiple words, e.g. "Tech Review", there is a conflict in the query.  The error occurs with the following text

...AND status = "Tech Review"", "has Epic")

I get the same error message if my query text is

...AND status in (Working, "Tech Review")", "has Epic")

The error is looking for a ")" but is seeing "Tech which throws an error in JIRA.

Does anyone have any suggestions on how I could expand the query to include multiple status or how to create a query that builds on one another to where I could create a query looking for Tech Review, Working, and other statuses?

Any assistance would be appreciated.

[David Young]

I also tried using this JQL Query and still got the same error.  It seems that the error is the additional quotation marks.

project in (Multiple Projects) AND issuetype = Epic AND resolution is EMPTY AND issueFunction in linkedIssuesOf("issuetype in (Enhancement, Bug, Research, Refactor, Story, Task) AND statusCategory in ("In Progress")", "has Epic")

The error I receive is:

Error in the JQL Query: Expecting ')' or ',' but got 'In'. (line 1, character 302)

The error seems to be focused around this part of the query:

AND statusCategory in ("In Progress")", "has Epic")

Does anyone know how to get results and based on what I'm looking for.

[Matthias Krakovsky]

you have to use single quotes within the statement

project in (Multiple Projects) AND issuetype = Epic AND resolution is EMPTY AND issueFunction in linkedIssuesOf("issuetype in (Enhancement, Bug, Research, Refactor, Story, Task) AND statusCategory in ('In Progress')", "has Epic")

[DPKJ]

This isn't straight forward, if you are using Jira Server/DC you can use ScriptRunner for this.

On cloud platform this seems not possible.

[David Young]

I get this is not straight forward and there might be a few filters I need to great, and then build on one another.  But I just need help figuring how to build them.

[DPKJ]

What platform are you on? Server/DC?

[David Young]

Server

[DPKJ]

I think you should go with @Muhammad Ramzan_Atlassian Certified Master_ answer 

[Muhammad Ramzan_Atlassian Certified Master_]

I think using default search you cannot achieve this, you need some plugin like this one where you can get the results as required.

for example,in your case using this plugin following query will give you the required results

This query will return all epics which have linked stories with any status of (Working, 'Tech Review', 'Integration QA', 'Ready for Demo', 'Ready in Master QA', 'Master QA'

issue in epicOf("issuetype = Story and Status in (Working, 'Tech Review', 'Integration QA', 'Ready for Demo', 'Ready in Master QA', 'Master QA')")

here is the documentation link for any further query you need.

https://jqlsearchextensions.atlassian.net/wiki/spaces/SEARCH/pages/30497099/JQL+Reference+Server#JQLReference(Server)-EpicOf(jql-query)

[David Young]

I have the ScriptRunner plugin and most ideas I've read feed through that, but you are correct, my initial query does not do the trick.  I need something different.  I tried the basic of your query and it returned the following error:

Unable to find JQL function 'epicOf(Status in (Working, 'Tech Review', 'Integration QA', 'Ready for Demo', 'Ready in Master QA', 'Master QA'))'.

Since I want to keep this pretty generic and let is cover various issue types as well as projects, this didn't work like I'd hoped.

[Muhammad Ramzan_Atlassian Certified Master_]

This query belongs to following plugin.

it will work if plugin is installed and regarding multiple issue types you can add issue types in query like this

issue in epicOf("issuetype in( Story,Task) and Status in (Working, 'Tech Review', 'Integration QA', 'Ready for Demo', 'Ready in Master QA', 'Master QA')")