Filter Structure by values in a Formula column

[David Doria]

Say I have a custom Formula column or one of the canned Structure columns like "Count Sub-Items". Is there a way to filter the view to show only items where that column matches a particular value?

And perhaps related - is there a way to get a count of the items that have a particular value in this sort of column?

Thanks,

David

[Stepan Kholodov _ALM Works_ · Accepted solution]

Hello David,

The Formula column can't be referenced in JQL directly, but you can first group issues by the Formula column using the Text Attribute... Group generator and selecting the needed Formula there and then you can use S-JQL query for filtering out groups that you don't need.

The 'Count Sub-items' column is pre-defined and can't be changed, but you can just create a new formula that can count only issues with particular values. The formula can look something similar to this: if(variable=value, 1). Where variable is the name of the field/column with values and value is the actual value, i.e. 1 will be returned for issues that have needed values. Then you can enable the 'Sum over sub-items' option in the Formula column to show the sum of such issues.

And we have a new major feature called Effectors that allows to write Structure attributes - such as the Formula column, the totals columns and others - to Jira fields, so it is also possible to just synchronize a calculated attribute with a Jira field and then to use it in JQL. Here is a video showing how something like can be done: https://youtu.be/QSreuWUQTp8

I hope this helps. If you need further assistance or have questions about anything else, please feel free to contact us directly at support.almworks.com and we'll be back with you shortly.

Best regards,
Stepan Kholodov
ALM Works

[David Doria]

Hi Stepan,

Hm, I added a Group automation and then set 'Group By' to 'Count Sub-Items'. The only group it seems to make is called 'No value', and it seems to contain everything.

I also tried to set 'Group By' to a custom formula that I have with binary ('0' and '1') output values. This time it creates a group called '1', but it still contains everything, even the items with value '0'.

Any suggestions?

David

[Stepan Kholodov _ALM Works_]

The grouping will not work with the 'Count Sub-items' column specifically because it returns no values for issues, it only returns it for their parents. But it should work for the mentioned custom formula.

It's hard to say why issues are not grouped correctly without seeing the applied formula and its results, the structure's hierarchy and the Group generator's configuration. It is possible that the grouping is not being done on the right level of the structure. Please send us the screenshots of the mentioned data here or create a ticket at our Service Desk if the data is too sensitive, and we will properly support you there.

With a formula that returns 1 and 0 for different issues, the results of grouping should create two corresponding groups. Then you can apply a Filter transformation with the S-JQL query: !selfordescendant of folder('0') where 0 is the group with unwanted issues. The filter removes the group as well as issues inside of it. The filter should also be configured to work on the level where the groups by the formula are created.

Best regards,
Stepan Kholodov
ALM Works

[David Doria]

The "Text Attribute" Group Transform (vs Group Automation) ended up doing what I was looking for. It doesn't directly "filter" by a particular value, but rather collects each value into a folder/"Group", and you can easily collapse the groups and expand the one you wanted to filter by.

Close enough for me!

[Stepan Kholodov _ALM Works_]

Once you have your issues grouped by the formula, you can add another transformation - the Filter transformation that allows removing certain created groups and all issues in them.
So filtering by a formula is a two-steps process: first, you need to group the results, and then you need to add filtering by groups.
In any case, it's good to know that you found the fitting solution!

Best regards,
Stepan Kholodov
ALM Works

[Janne Jaula]

There is a way around this (that may or may not help you in your use case) by using sibling relation as way to filter the results.

Example case "How to check if an issue has too many parents based on issue links" (in this case we have link type composition "Parent contains child issue <-> child issue is part of parent"

• You start by creating a structure and insert issues you want to check for too many parents (not so logically this will be shown as last constructor row while other rows apply from top to bottom).
• Then extend this structure by extending "upside down" hierarchy parent is part of children to show parents BELOW issues inserted to the structure.
• Then add s-jql filter: (sibling of (child of root)) and make that apply manually to LEVEL 2 that will remove all children from structure that have no siblings (so, those that have no siblings ("upside down parents") will be removed)
• Then add another s-jql filter: (parent of leaf) on current level (or manually to LEVEL 1 which is the same as current) to remove those roots with no children.
• NOTE that order in which s-jql filters applied in steps above is critical for end results! You can drag & drop them to correct order if for some reason they appeared in wrong order.
• Now you have a structure that shows only those issues with too many parents (and show the issues as sub items of these issues)

[Janne Jaula]

You can do the above successfully as TRANSFORMATIONS to produce the same results with group by text attribute count leaves and then filter out the folders with valid results but this will not work if put into a constructor and thus its a no go if you want to create a structure to be used as a trigger for e.g. scheduled automation for jira rule to comment on the issues that have too many parents (don't comply with your requirements model rules).

[Melanie Liguet]

hello,

I'm new on Structure.
I have the same need, I want to retrieve tickets without subtasks.
I use a formula to count the subtasks: Count#children { key}
but how to filter to have only the values at 0?
I have read your answers but I can't apply them. Can I have a concrete example?

Regards, 

[David Niro]

Hello @Melanie Liguet ,

David from ALM Works here.

Are you currently using Structure on-prem or cloud?  If you are using an on-prem version, you can accomplish your goal by adding a new Quick Transformation, specifically Filter by Attribute.  

In the Attribute field, you will choose the formula and add an "=0" to indicate this is the value you want to remain. 

Then, set the Operator to "is true"

Finally, select the level of your structure that you want to filter on.  In my example image, the subtasks were on level 4, so I set my Filter on Level to 3 (their "parents").

Please let me know if this helps!

Best,
David

[Melanie Liguet]

Hi, 

Thanks a lot for this quick answer.
I use Structure on cloud (sorry, it is a very important information)

regards,

[David Niro]

Hello @Melanie Liguet ,

You are very welcome.  

At the moment, the solution provided is not available on Cloud.  We do, however, have plans to introduce it.  

I have added your vote to our dev task and will follow-up with you here as soon as there an update to share.

Please also feel free to contact us directly via email support@almworks.com or our customer portal support.almworks.com.  We would be happy to hear from you.

Best,
David