Highlighting few cells in Structure

[Poojaa Sudhakaran]

Hello there,

I have a use case where we need to display count of stories in the project and highlight stories that are in cancelled status. Count of stories is achieved using a formula column with field type as "Number" and sum over subitems is enabled.

But highlighting Cancelled stories is were I'm facing issue. I'm able to highlight cancelled stories and count stories using the below formula.

Formula: 

If(issuetype = "story" and status = "Cancelled", "{panel:bgColor=#FFFF00}1{panel}"), issuetype = "story" and status != "Cancelled", 1)

Highlight works only if the column type is set as "Wiki Markup" but the sum over subitems fails in this case. If I set the column type as "Number", sum over subitems is working but highlight fails.

We can achieve this if we are highlighting the stories in a different formula column which is my last resort. But can these both be achieved in a single column?

Thanks,

Poojaa

[Philip Heijkoop _ALM Works_ · Accepted solution]

Hi Poojaa,

Phil here, from ALM Works (makers of Structure), I see we meet again on a formula conundrum. There is a middle ground option here, probably what you're looking for.

We can create a variable that we reference in this formula, that itself is another formula. You'd write your formula as normal, referencing this variable (not yet defined). When you get prompted for the variable definition, you scroll down the list of options to formula and a new editor will open in the same window and you can write a the other formula there. This gives you the benefit of two formula columns, without having to keep track of both!

Also, might be worth noting, you can create a separate column, reference it in a formula column, and then delete the first column. Structure copies by value here, so updates to that first column don't propagate (which means you'd need to re-reference if you do update the column), but in this case it also means you don't need to keep helper columns around after they've served their purpose.

Hope that helps!

Phil Heijkoop

Lead Solutions Engineer

ALM Works

[Poojaa Sudhakaran]

Thanks again Phil!

I tried the first approach - defining a formula within formula. In this approach, cells were highlighted but during sum over subitems, highlighted cells were ignored i.e., if there are 10 stories out of which 2 are cancelled (highlighted) the sum shows total count as 8 ignoring highlighted cells. I guess it's because they are in wiki format they got ignored.

But I was able to tweak the formula to include summation within formula instead of using "sum over subitems" option. This way I was able to get the sum as well as highlight cells

If(issuetype = "story" and status = "Cancelled", "{panel:bgColor=#FFFF00}1{panel}"),
issuetype = "story" and status != "Cancelled", 1,
itemtype != "issue", SUM{COUNT#children{IF(issuetype='story',1)}})

Thanks,

Poojaa