New updates in the on-prem edition of Structure — Structure 6.1 — allow users to create more powerful formulas, allowing for greater levels of customization in their calculations.

With the update, a variety of improvements help you upgrade formulas: You can use JQL to be incredibly precise with the information a Formula Column acts on, and you can use S-JQL to target specific areas in your structure’s hierarchy. Or, you can use a good, old-fashioned text query to match important keywords.

Then vs now

JQL has always been a core component of Jira. It's how Jira "thinks" about issues, allowing you to zero in on specific types of issues, issue attributes, or the relationships between issues. S-JQL is a Structure-specific extension of JQL, which allows you to focus on the locations of issues within the structure hierarchy.

In past versions of Structure, JQL and S-JQL were only used for building structures, defining their hierarchies, and filtering the results. Now, Formula Columns support JQL and S-JQL query matching. This means you can narrow the results of a formula by asking complex questions about each item in the structure — for example, you can limit your calculations to only issues from specific projects or stories with children within the structure.

Formula Columns with JQL

Formula Columns are great for doing complex calculations, but until now the only ways to limit which issues were included in that calculation was to build the entire structure board around those limitations or do a temporary transformation (which only worked if your formula was configured to ignore filtered items, and you always used the same transformation in the future).

Now you have much more flexibility in the way you limit the Formula Columns, freeing you up to build structures the way they suit you, rather than the way they suit your formulas.

Here's an example. Say you want to calculate the cost of a project. Just writing the formula “SUM{cost}” is likely not going to capture the level of detail you need — often in that situation, you'd have things you want to include or omit. For instance, you may have items in the structure that are purely for testing, or teams that you don’t want to include. This is where Query Match comes in.

In the example below, we’ve used a JQL Query Match to filter all issues in the project category TEST — which is a particularly useful filter, because project category is one of the few attributes you cannot include in a structure — as well as any issues assigned to the support group.

To accomplish this, we created a variable “not_internal” and assigned it to a Query Match. We used a JQL query to specify which issues we want to include (or, in this case, exclude).When the formula runs, it will calculate the cost of all items in the structure except those in TEST or assigned to Support.

JQL Query Match can be used to limit formulas based on any issue attribute or the relationships between issues — even issues that aren’t in the current structure. This is an unprecedented level of information access.

Formula columns with S-JQL

Using S-JQL in a Query Match allows you to go a step further, narrowing the results to items based on their locations within the structure hierarchy. For example, when estimating the work required to complete each epic, the work is often not based on the issue itself but the sum of all its children (items below it in the structure hierarchy).

Using S-JQL, you could write a formula that calculates the work for all children, but even that may not tell the complete story. Often stories themselves are broken into sub-tasks, and it’s those sub-tasks that actually determine the work for each story. In this case, to get an accurate estimate it’s not the children we need to look at, but the bottom-most level of each branch in our hierarchy, or the “leaves.”

Using the leaf constraint with an S-JQL Query Match, we can now focus in specifically on just the stories without children and the sub-tasks for those stories with children. This is often how we see work being broken out: The higher levels are some type of grouping relationship, and the lowest levels are the actual work that needs to be done. Depending on how the structure is configured, the details and overarching hierarchy might be very different, but this rule of thumb often holds true.

Here, the “leaf” is saying this issue has no children, so we know we are at the bottom of the hierarchy. We also removed epics from the calculation, as we might simply have ungroomed epics that don’t have children yet, so we want to omit them for a more accurate report.

These are both fairly simple examples. We could just as easily have several queries to narrow are results on highly specific issues, or even add multiple formulas to see if issues overlap multiple filters. Or if we don’t need to calculate anything, but simply need to identify issues based on a query, we could use the new Query Match column instead of a formula. No matter what information you need to present, query matching allows you to present information with fine precision, giving your users the necessary context to make informed decisions.

This opens a new avenue of questions to be asked and information to be included in a structure. What new questions will you be asking?

Bringing it all together with Wiki Markup

Now that you've read about how you can build a formula to dig up all kinds of specific information, consider how you're going to present your findings. Wiki markup can help make data easier to read by breaking the standard spreadsheet mold. By adding color or other visual aids, you can make sure your users easily find the relevant data.

This becomes even more important as the team is constantly inundated with more and more information. With some simple markup, you can make sure that the most critical information is eye catching and won’t be missed even by someone looking at a structure for the first time. When you glance at the below image, it is obvious that one item is high risk, even if you can’t read the words or know the underlying context.

However, you can also use wiki markup to clearly present complex information, too. The example below shows all of the different statuses as different colors, so it's still easy to see the overall status as well as how many are in each category. And on top of all that, this is done in three lines of code — making it about as complicated as an excel formula.

Altogether, these upgrades allow you to simply build a formula to target the information you need. JQL query matching limits formulas based on issue attributes, and S-JQL lets you seek out information based on where it's located in the structure hierarchy. Then, more easily present your finding in wiki markup. And good news: Wiki markup is now available in Structure Memos as part of the Structure 6.1 release.

Learn more about Structure 6.1 on the Atlassian Marketplace.