Need option to use summation of effort spent in subtask to filter stories in JQL

Question

I have a simple JIRA project with few stories, sub-tasks for each story and sprints. Here is the general structure we follow:

We have a User Story: ProjKey-01

And we have created subtasks under the story as below.

Subtask: ProjKey-2

Subtask: ProjKey-3

Subtask: ProjKey-4

Subtask: ProjKey-5

The team is logging effort against the sub-tasks. The stories and subtasks have simple workflow or TODO, INPROGRESS and DONE.

Our need is, whenever someone starts working on any subtask, they should move the subtask to INPROGRESS.  This is happening correctly with team members updating the subtask status correctly.

We have one more need: the first person who starts working on any story should move the story to INPROGRESS. This is where we are not disciplined. So we start working on a story - its subtasks has effort logged against it, status is correctly updated for the subtask but the story is still in TODO status. I so wish JIRA automatically updates the story status as TODO (like Rally).

I want to filter out all such stories where we have this mismatch. Any suggestions how I can achieve this?

One way I was thinking is: to filter stories which have some effort logged against those. But since we log effort against sub-tasks, if I use timeSpent as filter criteria, the story doesn't show up. I need a filter criteria that will allow me to use sum of effort logged under the story. Any idea if this is possible in JIRA?

Vlad[ALM Works] · Answer

Hi Anil!

If you are still looking for a solution, please consider giving Structure for Jira a try. Our app’s Synchronization feature can help you accomplish the task you are describing here.

Specifically, the Status Rollup Synchronizer automatically aggregates statuses of sub-issues and updates the status of the parent issue accordingly. For example, you can make it so a parent issue’s Status is set to ‘Resolved’ if all sub-issues are ‘Resolved’.

You can read more about it here.

Structure for Jira is also packed with other easy to use features that help Atlassian's largest customers track, manage, and visualize progress across projects. Currently, it is compatible with Jira Server (Data Center and Service Desk) but we are actively working on developing a version for Jira Cloud.

I’d be more than happy to tell you more about Structure, if interested. Please feel free to reach out to our support team at support@almworks.com or you can email me directly at vlad@almworks.com if you have any questions.

Cheers!
Vlad
ALM Works, Inc.
www.almworks.com

Alexey Matveev · Answer

You would need an add-on like Power Scripts or Adaptivist ScriptRunner to run a JQL query which would show all stories in the  TO DO status and their subtasks are in the  In Progress status.

For example, if you have Adaptivist ScriptRunner you could use the following query

issuefunction in parentsOf("status = \"In Progress\"") and status = "To Do"

If you have Power Scripts you would need to write your own JQL function. If you have the add-on I coud help you with the script.