How to sum timespent of subtasks against estimate of main story?

Question

Hi everyone,

I am trying to set up a JQL search filter for my dashboard that shows only stories with these characteristics:

- certain project

- only stories

- currently running sprint

- workratio larger than 100 (more timespent than estimate)

- status is not Done

This is the syntax I am using, but it only returns those stories that do not have sub tasks which are being used to add spent time. Those stories where people use the subtasks are not tracked here. (For stories with sub tasks we have an estimate on the story and remaining estimate is alway 0, so that the sub tasks aggregate to the sum of the story estimate).

project = theproject AND issuetype = Story AND sprint in openSprints() AND workRatio > 100 AND status != Done

Can anyone help me out here? I feel like it should not be to much work left to finish the query.

Cheers

Walter Buggenhout · Answer

Hi @c3po16 and welcome to the Community!

I am afraid this approach won't work. Workratio is a very good (and often overlooked) way to compare original estimate to time spent, but it is only capable of comparing those fields on a single issue, it can't include the details from its sub-tasks.

You can include sub-tasks in your query by simply extending it this way:

project = theproject AND issuetype IN (Story, sub-Task)
AND sprint in openSprints() AND workRatio > 100 AND status != Done

But if you have your estimates at story level, you probably don't have any on your sub-tasks. So that is probably only a half-baked solution.

To use workratio ánd your original query, you would need to get your timespent on your story instead of on the sub-tasks. You can either let your team track their time spent on the story directly (only using the sub-tasks to track progress by status) or you could set up an automation rule to sum the time spent from the sub-tasks into their parent issue. An obvious downside of that approach would be that this may get counted twice in reporting.

Hope this helps!