Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Filter Structure by values in a Formula column

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?



1 answer

2 votes

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:

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

Best regards,
Stepan Kholodov
ALM Works

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?


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

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!

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

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)

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).

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Apps & Integrations

Marketplace Partner Spotlight: Fine Software

Happy New Year, everybody! What better way to kick off 2022 than with a new Marketplace Partner Spotlight ✨ This month, we're getting to know Daniel Franz, co-founder of Fine Software. Fine Software ...

381 views 2 15
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you