Automation concepts – Percentage Complete

[#1]

Estimated time to read: 6 minutes

TL;DR: Community members often ask how to calculate a percentage complete value, such as for an Epic's child items. This article outlines one approach using a single Lookup Work Items action, and potential challenges to manage with the rule.

Why do this?

Jira Cloud does not have a general purpose, built-in feature to calculate fields for customers. In some features, application code is used to calculate things such as percentage completed for a set of work items. Unfortunately, those values cannot always be accessed directly, such as in JQL searches, dashboards, etc.

Automation rules provide a workaround for this...provided one considers potential edge cases and challenges. We will cover those at the end of this article.

How to do this

We will use the Lookup Work Items action with JQL to gather the work items. In our case, we are looking for the sibling, child work items of an Epic.

Thus, an example rule to update an Epic's custom field Percentage Complete when a child work item transitions status would be this:

• trigger: work item transitioned
• issue fields condition: issue type is one of Story, Task, Bug (or whatever your child types are)
• smart values condition: {{issue.parent}} is not empty
• action: lookup work items, with JQL of

parent = {{issue.parent.key}}

• branch, related work items: to parent
  • action: edit work item, setting the Percentage Complete to this smart value expression

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}

How that works is:

1. iterate over the child work items
2. filtering them with a conditional expression for items in a "Done" Status Category
3. adding +1 to the result
4. with a 0 at the front as a default value
5. wrapping in parentheses to create the sum count
6. dividing that by the total number found (i.e., the size of the list)
7. multiplying that by 100 to create a percentage value
8. rounding to 0 decimal places
9. finally wrapping all of that in a math expression

That will calculate the percentage by count of items. When instead one wants the percentage by the Story Point values, substitute that field to find the sums rather than the counts.

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+{{Story points|0}}{{/}}{{/}} ) / {{lookupIssues.Story points.sum|0}} * 100, 0){{/}}

Some edge cases, challenges, etc.

Accuracy

Several things could impact the accuracy of the percentage complete. One needs to either: create multiply rules to calculate the value, or use a single rule with a Scheduled Trigger to periodically calculate the value. Those cases include at least these:

1. When the child work item of an Epic transitions status, update the Epic's percentage complete
2. When a child's Story Points change, ... (if using Story Points rather than counts)
3. When a new child is created and added to an Epic, ...
4. When an existing child is added to an Epic, ...
5. When an existing child is removed from an Epic, update the previous parent Epic's field
6. When a child changes to a new parent, update both Epics
7. When a child work item is deleted, update the previous parent
8. When a child work item's type changes, update the Epic(s) when necessary
9. When someone manually tampers with the percentage in the Epic, recalculate it
10. When there is an Atlassian outage preventing rules from running, they may not all trigger later. As a precaution, create a Scheduled Trigger rule which will update all relevant Epics.
11. When using multiple rules would exceed your automation monthly limits, use the single scheduled rule in #10 above
12. etc.

Some of these cases may be combined to reduce the number of rules.

Too many work items for Lookup Work Items

Lookup Work Items can only return up to 100 items. The REST API endpoint used by rules has this limit, with no workarounds, except...

The new search endpoint supports up to 5000 items, but it can only return the ID value for anything more than 100 work items. A work around would be to change the rule to use the Send Web Request action to call the endpoint twice:

• call once to find the "done" items, storing the count in with Create Variable
• call again to get the total count
• finally, performing the division on the two values

This edge case is a bit extreme; when you want a percentage for this many items, consider instead using marketplace apps or advanced planning features of Jira.

Multiple work item type levels to update

What if I want to calculate the percentage values at multiple work item levels, such as Subtasks to Story, Story to Epics, etc.?

Since the changes to use the Parent field for parent / child relationships, the rule could be modified to handle any such pairing of types. And, the rule trigger could be modified to allow rule trigger, so the percentage updates could cascade. There are two more things to note when this is attempted:

1. Rules have a looping service limit, and so if the rule recursively triggers too many times, it could stop processing. This could happen in the second note...
2. Remember our accuracy challenge from earlier? With manual tampering and multiple work item levels, multiple "fixes" are required. That could lead to a looping limit when handled in one single rule.

Wrapping it up!

Thank you for reading all the way to here! Hopefully you learned some new automation ideas. Please let me know your feedback and improvement ideas for the article, and as always...

Happy rule writing!