Structure for Jira custom column

[sh0716_lee]

Hello. In my Structure, I would like to add a column that displays a value from the task's siblings issue. Is it possible to do this as a custom column in Structure?

For example:

Issue A has children issue a, b, c
A has a custom field called Total 'workload'
if each issue has workload data like below

A Total workload : 90 (I would like to display like this)
    a workload : 10
    b workload : 20
    c workload : 30
B Total workload : 90
    d workload : 10
    e workload : 20

I would like to display A's Total workload value not 60(a+b+c) but 90 (a+b+c+d+e : add siblings's children workload)
And Also B's total workload not 30(d+e) but 90(a+b+c+d+e : add siblings's children workload)


PS : I tried to get value A Total workload using A's parent's Total workload, but it is not possible.

Thanks in advance

[Ignacio Pulgar]

Hi @sh0716_lee ,

If I'm understanding it correctly, you would like to set a column like the one titled Formula in the following picture:

The n3 column in the screenshot is your workload number custom field.

Just follow these steps in order to achieve the resulting Formula column:

1. Add a column of type Formula, change its header name to n1 and set its formula to:
   
   IF(depth = 1, SUM{workload})
   
   
2. Add a column of type Formula, change its header name to n2 and set its formula to:
   
   IF(depth = 2, PARENT{n1})
   
   On saving that formula, n1 will not be recognised automatically and will become red, so, click on it and map it to Used in columns > Formula(n1). Save this formula.
   
   
3. Add a column of type Formula (we will keep that name, as that's the target column) and set its formula to:
   
   CASE(depth,
       1, n1,
       2, n2,
       3, workload
   )
   
   Map n1 to column n1 and n2 to column n2, in a similar way you did in the previous step.

At this point, the column named Formula will display the desired numbers.

Once you get the Formula column, delete columns n1 and n2. Don't worry! Your Formula column won't break, as it already contains a copy of the n1 and n2 formulae, not a reference to the columns.

Hope it helps.

[sh0716_lee]

Thanks for your help~!

I tried to make up structure field according to your advice.
But "n2" value won't display like your guide.

I thought formula field(ex.n1) could not using Parent Command .
ex.) IF(depth = 2, PARENT{n1})
Is it possible to use PARENT command with formula field like "n1"?
I used Structure 5.6.0 version.

[Egor Tasa [ALM Works]]

Hi Ignacio,

I must admit I was surprised that construct PARENT{SUM{}} worked for you, for now, this combination should not work (as should not SUM{PARENT{}}). Could it be that the formula in n1 was changed after being referenced from n2?

Also, I must note that depth is a volatile attribute. Just click the Automation button and depths will change. Add a grouper and it will change also.

Sorry to say, but until improvements to the expression language are delivered there is no solution to this via Formulas.

Regards,
Egor Tasa

ALM Works

[Ignacio Pulgar]

Hi @sh0716_lee ,

It should work.

Does it return any errors? Is there anything in red color?

If no errors are being thrown, then it might be due to having applied an incorrect depth level.

In order to be sure, add a new formula column just with this formula:

depth

After that, ensure that n2 formula has depth = (the appropriate level)

Delete n2 column and create it from scratch as described in step 2.

Should that approach not work, please, share a screenshot of the error, if any.

[Ignacio Pulgar]

Hi @Egor Tasa [ALM Works] ,

I suspect it might have something to do with the way Structure process open and closure of curly brackets {{}} 

Please, try to follow the steps described to replicate the same solution and check it's a working solution with no changes of formulae after having copied the column.

Of course, depth may change if you alter the Automation rules, but it is perfect for explaining this example.

The solution also works by substituting depth with issuetype, ie:

• depth = 1 changed to type = "Epic"
• depth = 2 changed to type = "Story"
• depth = 3 changed to type = "Sub-task"

By the way, some time ago, I noticed that creating variables directly in a formula didn't seem to work, but the solution worked by discompounding variables in columns, and then adding the variables in the big formula through the Used in columns method.

That behaviour was really strange, as copy+pasting the formula columns to the big formula variables did not seem to work. However, clicking on the red variable names and selecting the appropriate Used in columns column made the solution work.

Maybe there's something different in the way Structure processes both actions. Or maybe it's just an error I consistently commited over and over again along different days and formulae... and it was just that showing the result of variable in a column is just a great way for debugging a big formula.

Anyway, that strategy made several solutions to work in Structure 5.4 and 5.6.3 Data Center, and in Structure 6.0 EAP Server versions, not finding so far any versions were that approach didn't work.

Hope it clarifies anything.

[Egor Tasa [ALM Works]]

Hi Ignacio,

For me, it also returns a blank result (no error messages, summary of reference or reference to summary simply return blank). Same thing is with variables, you can use aggregation in a variable, but not a variable in aggregation. 

Maybe we could look at your View's active specification to find what may be happening there, but doing this in a community post will just clutter up things with technical details.

Regards,
Egor

[Ignacio Pulgar]

I have reproduced the blank and not-working result on a JSW Data Center 8.2.3 instance with Structure 5.6.3.

The working solution I placed here has been achieved on a local installation (in my laptop) of Jira Server 8.6.1 with Structure 6.0 EAP. I confirm in this instance the following syntax works like a charm: PARENT{SUM{storypoints}}

Have been testing a bit but don't have a clue on why it works on a system while it doesn't work on the other.

Just to be on the same page, it would be great if anyone else could test the formula against Structure 6.0 EAP.

Best regards

[sh0716_lee]

Hi Ignacio

I made up structure like below.
There's no any errors in n2 , and I rechecked depth value.

(I could not attach my structure picture. so I draw it myself. ^^;;;)

It's part of the structure. 

I want to display C, D, E 's n2 value is 190 and B's n2 value is 480

  summary                       n3             n1              n2            depth

   A project                                                                              1

        Project Type           600                                                   2

                AAA                                 480                                  3

                        B              0              190                                  4

                             C        50                                                     5

                             D        50                                                     5 

                             E         50                                                     5

                               ,,,,,,,,

n1's formula to : IF(depth = 3 or depth = 4, SUM{n3})
n2's formula to : IF(depth = 5, PARENT{n1})

what's the problem in my structure?
Please, give me an advice.

Thanks

[Egor Tasa [ALM Works]]

Hi All,

Structure 6.0 is now available through Marketplace, and yes, the improvement for aggregation functions is there. So the PARENT{SUM{}} or SUM{PARENT{}} should work after you upgrade. v.5.6.3 does not allow this. 

Regards,
Egor

[Ignacio Pulgar]

Those are awesome news! We've just planned the upgrade! :)

Thank you!

[Egor Tasa [ALM Works]]

Hi,

Let me add my 5 cents here. First of all, currently constructs like SUM{PARENT{}} won't work. We are planning to remove this limitation or create a workaround, but at the moment it is simply not possible. Perhaps in one of the nearest releases we will have a solution, but no guarantee at the moment.

Regards,
Egor Tasa

ALM Works

[JimmyVanAU]

Thanks Egor :)

[JimmyVanAU]

I haven't got an instance of Jira running with Structure, but it looks like you'll be using the PARENT function:

PARENT {SUM {workload}}

or something to that effect. This may also display against all sub-tasks/epics, so if you want it only for the stories, consider something like:

if(type='story'; PARENT {SUM {workload}})

Documentation links are at:

https://wiki.almworks.com/display/structure/Aggregate+Function+Reference#AggregateFunctionReference-PARENT and

https://wiki.almworks.com/display/structure/Formula+Columns

Hope that helps!

[JimmyVanAU]

Could you provide a little more information about your problem?

Are A and B related in any way (issue links)? What if there is an issue C, with sub-tasks f and g. Should it sum those also?

[sh0716_lee]

A and B have same parent issue(AA) like this.
AA
   A Total workload : 90 (I would like to display like this)
      a workload : 10
      b workload : 20
      c workload : 30
  B Total workload : 90
      d workload : 10
      e workload : 20


As you said, if there is an issue C, I would like to sum C's Total Workload like this.

AA
   A Total workload : 100 (I would like to display like this)
      a workload : 10
      b workload : 20
      c workload : 30
  B Total workload : 100
      d workload : 10
      e workload : 20
  C Total workload : 100
      f workload : 5
     g workload : 5

Thanks!

[JimmyVanAU]

Ah nice, so can you tell me about the relationship between AA and A? I am assuming that AA <-> B and AA <-> C are also identical in relationship.

Is AA the epic, and A/B/C the story? a,b,c,d,e,f,g are all sub-tasks of a story. Is that right?

[sh0716_lee]

I already tried as you mentioned it, but I couldn't  find the result.

what's the problem below syntax?

if (
issuetype='K1' ; sum#children{sumworkload};
issuetype ='K2'; Parent{sum{Realworkload}};
""
)

A and B have same parent issue(AA) like this.
AA  issuetype: K1
   A Total workload : 90 (I would like to display like this)  issuetype: K2
      a workload : 10
      b workload : 20
      c workload : 30
  B Total workload : 90
      d workload : 10
      e workload : 20

Thanks!

[JimmyVanAU]

Do you get an error message, or do they come out blank (always resulting in default value)?

The formula:

if (
  issuetype='K1' ; sum#children{sumworkload};
  issuetype ='K2'; Parent{sum{Realworkload}};
""
)

looks to be pointing to two different fields. Is that right?

I would have guessed something more along the lines of:

if (
  issuetype = 'K1' ; sum#children{workload};
  issuetype = 'K2'; Parent{sum{workload}};
""
)

Do the formulas work independently?

if (
  issuetype='K1' ; sum#children{workload};
  ""
)

and

if (
  issuetype ='K2'; Parent{sum{workload}};
  ""
)

If they do, then this might be a manifestation of - 

It is not possible to include both upward-looking and downward-looking aggregate functions within the same formula. When using one of the two upward-looking aggregate functions, PARENT and JOIN (when used with an upward-looking modifier), you cannot include any of the other aggregate functions listed above.

For example, the formula for calculating the percentage of Story Points of an issue compared to the aggregate Story Points of itsparent ( story_points / PARENT {SUM {story_points}} ) would fail, because PARENT looks one level up in your hierarchy, while SUM aggregates the levels below.

We are working to fix this limitation in a future version.

from https://wiki.almworks.com/display/structure/Aggregate+Function+Reference since you're calculating up and down.

Best to raise a support ticket with the ALM guys and see if they have any ideas :)

[sh0716_lee]

At First, there was typo.

My formula sentence , like below 

if (
  issuetype = 'K1' ; sum#children{workload};
  issuetype = 'K2'; Parent{sum{workload}};
""
)

it didn't work independently as i intended.

I guess the reason that workload field's  checked 'Sum over sub-items' option.

I am looking forward to working this in a future version.

Thanks for your help~!

[JimmyVanAU]

No worries at all. Sorry we couldn't get to the bottom of it. Raise a ticket and give them the team a nudge from Jimmy :)