Automation Rule - calculate progress based on children of linked epics

[kenneth.gomes]

I am trying to create an automation between two projects. The current JIRA hierarchy we follow is as below;

Portfolio Epic > Epic > User Story/Task

Problem Statement:

I have two projects Project A and Project B

• Project A - Portfolio Epic
  • Epic 1
  • Epic 2
  • Epic 3
• Project B - Portfolio Epic 
  • Epic 1
  • Epic 2
  • Epic 3

I have an Epic in Project A - Epic 1 that is linked via "Related To" link to the overall Portfolio Epic in Project B. Both projects have two different project keys.

I am trying to set up an automation that looks at Epic 1 (Project A) and based on the "Relates To" link picks up the children in Project B - Portfolio Epic and sums up the story points and the % complete on children tickets and writes it back to Project A - Epic 1.

What I have got so far:

Currently the Automation is manually triggered while I test it out and I will move it to scheduled or auto triggered once I have it to where I need it.

Story points is calculated using the following - {{lookupIssues.Story Points.sum}}

At this point although the above rule is supposed to work it returns no value as I suspect the following may be blocking it. This automation only works if the rule is global; as project specific rules restrict the look up clause above. I need help validating my theory.

Second part to my problem is that I am unable figure out if there is a way to get the progress % over to Project A - Epic 1 from Project B

Any help and/or guidance would be greatly appreciated.

[Mark Segall · Accepted solution]

Hi @kenneth.gomes - You are correct that this needs to be a multiple project rule scope to span across your two projects.  Once the scope is updated, you should have no issue with your second problem.

Be warned though that Relates To is a nasty link type when you're trying to automate because there is no "parent/child" relationship as you have with Blocks/Is Blocked By and Clones/Is Cloned By link types.  This could cause some pain points in your automation.

[kenneth.gomes]

Thank you for helping me validate my theory

[Bill Sheboy · Accepted solution]

Hi @kenneth.gomes 

Your hypothesis is correct about the rule scope: a lookup issues action (or branch, etc.) can only see the issues within the selected scope of the rule.

Next regarding the % progress...first how do you want to measure progress?  The image you show is of a parent and child issues.  That progress indicator is by the status category of issues.  There are other ways to measure progress, such as based on the aggregate of your estimation values.

Once you have selected your progress method, you could use the issues in the lookup issues and smart value list filtering (or separate lookup calls) to compute the result.

Kind regards,
Bill

[kenneth.gomes]

Hi Bill,

Could you point me to the function that would help me aggregate the estimation values?

[Bill Sheboy]

Certainly!  Here are a couple of examples, using the math functions and smart value list filtering, with references below...

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/

https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588

For the same percentage the parent/child shows based on count:

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

To instead do this by story points, try this:

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

How these work...

• using a lookup of all of the issues
• first use smart value list filter to find the ones with a Status Category of done; for those items, add a value followed by a plus sign
• the above value is wrapped in parenthesis, so it can be divided by the total for the entire lookup result
• that value is then multiplied by 100 and rounded so it appears as a percentage, 0 to 100
• finally everything is wrapped in the math operators
• note inside of the rounding operation...an extra "0+" and trailing "0" are added.  This handles the case when the lookup returns nothing.

[kenneth.gomes]

Hi Bill,

I got access to run the rule globally but for some reason the story points are not being summed up. I haven't used your formula yet but instead I tried to keep it simple by using {{lookupissues.Story Points.sum}} but the automation still wont sum up the story points.

Log

Based on the automation it is able to pick up the linked PE but it is unable to sum up the epics underneath it.

CL - 6372 is a Epic

UIP-6 is a Portfolio Epic

I am trying to sum up the child issues underneath them.

Any suggestion or help would be greatly appreciated.

Thanks

[Bill Sheboy]

I notice two things: there is an extra space before the smart value and the case is incorrect.  FYI that smart values are name, spacing, and case sensitive.

Please try this one if you are using a company-managed project (CMP):

{{lookupIssues.Story points.sum|0}}

If instead you are using a team-managed project (TMP), they use Story point estimate:

{{lookupIssues.Story point estimate.sum|0}}

I also added the default value of zero (0) so even if the fields are empty you will get a number.

[kenneth.gomes]

Hi Bill,

I tried the changes you recommended but I am still unable to display the sum. 

I tried everything at this point I am not sure what is wrong with the automation.

[Bill Sheboy]

Please post an image of your complete, current rule.  That may help to explain what is happening.  Thanks!

[kenneth.gomes]

Hi Bill,

Here is the complete rule;

I trigger it manually from this epic

The look up is able to look up the related to ticket i.e. UIP-6

But it is unable to lookup the children within UIP-6 and sum up the SP values.

[kenneth.gomes]

Hi Bill,

I did some more research and it seems the above formula only works for regular projects not next gen projects. It seems I would need to look up how to automation rules work for next gen projects

Ken

[Bill Sheboy]

Did you see my note about using "Story point estimate" rather than "Story points"?  That should work for a team-managed project (formerly called NextGen).

{{lookupIssues.Story point estimate.sum|0}}

Next, you appear to be trying to sum child issues of the epic, not the linked issues.

Please try changing the Lookup Issues JQL to  this:

parent = {{triggerIssue.key}}

[Coco S Chaussée]

@Bill Sheboy - 

I have been struggling with a similar problem...

This is for company-managed projects.  I want to calculate % complete on Epics based on the number of children.  I have created an automation rule using your magic formula 

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

but get this error

Your help would be greatly appreciated.

[Bill Sheboy]

Hi @Coco S Chaussée 

Looking at your expression, there is a parenthesis in the wrong place.  It appears my original post had an error, and I have corrected that.  Tip: it helps to create expressions like this in a text editor which shows matched / mismatched parentheses and brackets.

Here is the corrected one:

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

It can also help to build expressions like this, piece by piece, and then combine them.  Here is an updated version of how I did that (except with the plus signs added at the front rather than behind the ones).

Completed issues count:

( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} )

Total issues count:

{{lookupIssues.size|0}}

The percentage divides those two values:

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

Finally, the percentage, rounded to a whole number would be:

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

Kind regards,
Bill

[Coco S Chaussée]

Perfect!  It works!!! 

Thank you so much for sharing your knowledge and for a quick turn-around.

[Coco S Chaussée]

@Bill Sheboy - is there a limit on the number of children returned?  I stumbled on an epic that has over 200 stories, but the formula only returns 100.

Any way to work around this?

Thank you

Coco

[Bill Sheboy]

There is no way I know of around that: there is an automation rule limit of up to 100 issues for lookup issues and other aspects of rules have other limits: https://community.atlassian.com/t5/Jira-articles/Automation-for-Jira-Send-web-request-using-Jira-REST-API/ba-p/1443828

If you often expect more than 100 issues, you may need to define a predictable way to break this up into pieces, with additional JQL criteria, save the results in variables, and sum up later.  For example, by labels, components, etc.

[Erin Ehlers]

Duplicative post.

[Erin Ehlers]

HI @Bill Sheboy ! 

Thank you so much for explaining how you got 

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

I am trying to also remove any issues in status "Not going to do" or "Cancelled" from the equation all together but having a hard time getting the syntax correct. Any help would be greatly appreciated! 

[Bill Sheboy]

HI @Erin Ehlers -- Welcome to the Atlassian Community!

First thing...for older threads it can help to create a new question and add a link to original ones.  That helps ensure more people see the question to offer suggestions.

Back to your question...

You can use various conditional logic functions to do that, such as not() and and() .  I recommend doing this step by step to get the logic how you want it, testing as you go.  For example:

and( not(equals(status.name, "Not going to do")), not(equals(status.name, "Cancelled")) )

Then that can be used to substitute into then entire expresssion:

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(and( not(equals(status.name, "Not going to do")), not(equals(status.name, "Cancelled")) ))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}

Please check the case of your status names match the expression exactly, or add the toLowerCase() function force them to a known value.

Kind regards,
Bill