image
See how Jira and Innovalog's workflow automation can help
9 minutes reading time (1821 words)

Automate on-the-fly calculations, such as work cost estimates, in less than an hour

guest-blog-post

Introduction (By Innovalog):

In your Jira projects, you might need to perform calculations based on data readily available in your Jira custom fields. While Jira gives you the ability to create custom fields, it doesn't give you the ability to perform calculations, even reasonably straightforward ones.

Say you need to calculate work cost estimates. Creating custom fields that record "hours worked" and "hourly fee" is not difficult in Jira. However, performing a fairly straightforward calculation on the cost of the project ("hours worked" * "hourly fee") is something that cannot be done automatically in Jira. While your team members can technically make this type of calculation manually, it is not the best approach for several reasons. It is an extra step in a process and is error-prone. And most importantly, the field is not responsive to changes. For example, if the number of hours worked increases or the hourly fee changes, so should the cost. Yet, it will not, unless you do this on-the-fly, automatically, with a calculated custom field.

But what are calculated custom fields? In a nutshell, calculated custom fields are NOT your regular custom fields, as they compute values on the fly instead of storing it in the Jira database. The value is recalculated every time the issue is displayed or updated. You can then display it on issues, dashboards, and even create reports that help you make better business decisions and estimation. Basically, this field can be used anywhere in Jira, including in JQL searches in the Issue Navigator, Saved Filters, Agile Board queries, etc.

Jira, however, doesn't offer you an option of performing these types of calculations, but you can easily do so with Jira Misc Custom Fields (JMCF). JCMF gives you access to many new Calculated Custom Field types in Jira Server and Data Center. While JMCF can be used for creating calculated custom fields as described here, it is generally used for exposing any kind of information not readily available in Jira. For example, you can display data from a parent issue, show who made the last change on an issue, expose information about a particular transition (who executed it and when), or calculate how long an issue remained in a particular status and display it on your Jira board.

Roy's blog post below shows an example of how a calculation can be done – with step-by-step instructions. Of course, it can be adapted to many of your business scenarios, such as calculating cost across various projects and calculating estimates for future projects based on related projects. Plus, this example will show you how simple it is to use our Editor and Tester, even if you know nothing about Groovy. You can simply click your way to a perfect business solution.


How to Create a Calculated Field

(for Jira Server and Data Center)

By Rynder Roy Klomp

What is the Business Need?
Jira comes with a number of standard system fields. It also gives you a flexible and broad means of creating your own custom fields. But what it doesn't do is give you the ability to create custom fields that will perform calculations. Don't bother looking or experimenting, you just cannot.

How does JMCF fill the Business Need?
To me, this is pretty simple. If you need the ability to calculate fields, then you need "Jira Misc Custom Fields" (commonly known as JMCF).

How does the business benefit?

If your process needs calculations, then JMCF will speed up the work process as you can now calculate things automatically rather than having to do the calculation on other devices, apps, or calculate manually. This frees up your team member to do more productive value-added work, reduces manual calculation errors, and probably improves morale.

In addition, as it probably takes, at a maximum, 1 (one) hour or less to build this solution. The cost/benefit is incredible!

Create a "Work Cost Estimate" Calculated Field

In this blog, we want to demonstrate a "work cost estimate" calculation operation. We will have a Cost per Hour field, a Number of Hours field, and a Total field, which will multiply the cost and hours fields.

We need to start by creating three custom Number fields.

Create three custom fields

The first thing to do is create two custom number fields (you probably have a "Sandbox" project somewhere, so I recommend you use that for this exercise).

  1. Create a custom Number Field, name it "Hours Work Estimate"
  2. Create a custom Number Field, name it "Hourly Fee"
  3. In the Advanced area of the Custom Fields screen, Create a custom Calculated (scripted) Number field (JMCF app)
  4. Make sure to associate them to a screen, etc.

We have now created our three custom fields. Two are standard Jira fields, and one is the JMCF Calculated (scripted) Number Field. The first two are our 'base' fields, and the JMCF field will show the results after calculating the two base fields.

Create Custom Field Formula

Next, we are going to create a formula (script) for our JMCF custom field and then test it.

When creating this formula, we need to know three things:

  • The custom fields involved
  • What to do with each of those fields
  • The operator involved (e.g. Add(+), Subtract (-), multiply (*), or divide (/)

    1. Navigate to the Main Menu Bar > Administration Cog, and select Issues
    2. In Administration > Issues, select Custom Fields in the sidebar
    3. In the Custom fields screen, find the Total Quote field and click on the Cog in the Actions column
    4. In the Cog drop-down, select configure
    5. In the Calculated (scripted) Number Field, click on Edit Groovy Formula:

6. In the Configure Formula screen, click on Issue Fields:

7. In the drop-down that appears, click on the Select a field drop-down arrow:

8. In the drop-down field, enter Hours estimate and select from the list:

9. Click on ID in the Refer to the issue field by options

10. In the list of value options that appear select Issue .get("customfield_#####")

NOTE: ##### represents the field ID for the "Hours Estimate" field in your version of Jira:

11. As a result, you should see this:

What we did

Let's take a short break here and look at what we did.

First, we entered the name of the first custom field we need in our calculation. By the way, you could have first picked Hourly Fee, it doesn't matter the order.

Second, we decided to have the formula use the field ID instead of the field name. I did this for two reasons. If the formula includes the Name, then any time you change the field name (it happens more than you might expect), you need to go back in and change the formula name as well. By using the field ID, you don't need to do anything if you change the field name. In addition, if you are in a company with many Jira or Project Admins you may end up with many fields named the same thing. If you use field name in the formula, and someone else creates a custom field with the same name, your formula won't work, because it won't know which field to choose.

Finally, we get to the intimidating part. All those expressions that look like I.T. coding. In this case we chose Issue.get("customfield_10402"). The formula means, "In this issue, get the value that is in custom field 10402". In our exercise custom field 10402 is the field ID for Hours Estimate in my version of Jira. Your ID is probably different.

We are one third of the way home, so let's continue.

12 .In the formula bar enter " * " after the formula (just enter the asterisk, not the quote marks):

13. Repeat steps 6 to 11 and in this case select Hourly Fee as the field

14. When done you should see this:

Let's finish by testing our formula and saving the script.

15. Click on the Test Groovy script…. link:

16. In the Test Groovy Script pop up, enter the Issue Key you wrote down in the Issue Key field and click Test

17. If everything is good, you should see something like this:

18. If everything worked, click Save to save your script Formula.

If the script didn't work, you probably got enthusiastic and rushed through the instructions. Don't worry, it happens often. Go back and start over, being very careful to follow the instructions as written.

Format Calculated Field

Our calculated formula works, but looks somewhat odd without a currency symbol. How do we know if the result is US currency, Euro, Yen, Pound, Yuan, Rupee, or that of any other country? Or maybe it is indeed just supposed to be a number.

Let us format our calculated field to include a $ sign.

1. Navigate to the Main Menu Bar > Administration Cog, and select Issues

2. In Administration > Issues, select Custom Fields in the sidebar

3. In the Custom fields screen, find the Total Quote field and click on the Cog in the Actions column

4. In the Cog dropdown, select configure

5. In the Calculated (scripted) Number Field, click on Edit Format Expression:

6. In the configure Number Formatting screen, find numberTool.format ('currency',value). If you don't see the image below, click on Expected Value in the Help line.
7. Copy and paste it into the expression line

8. In the expression, replace 'currency' with '$'.

9. Click Test Groovy Script, and you should see this:

NOTE: if you get an error message, replace '$' with '/$'

10. Click Save

What we did

When we opened the Configure Number Screen, we were shown a quantity of formula snippets. I find it easiest simply to copy and paste (making sure to leave no spaces before and after the formula), and then edit the formula.

This particular formula has four parts:

  • numberTool is the object we want to work with in the calculated issue field
  • format is what we want to do using the numberTool
  • 'currency' is the place holder for the currency symbol
  • value means that the field will show the value of the field after the currency symbol.


Make sure to include the single quotation marks before and after the currency symbol, otherwise you will get an error message.

The first time you try this formula you may get an error message. That's because the $ symbol means something specific in software coding, and the program might get confused. In this case you need to add another symbol to show the software that you want to use it for its original purpose: currency symbol. If you do get the error, then change ' to '/ . Typically, for some strange reason, the next time you format a similar number you just need to type the original ' . The program seems to recognize what you are trying to do.


Jira workflow extension cheat sheets are here
A simple guide to choosing the right Jira workflow...

Related Posts

 

Comments

No comments made yet. Be the first to submit a comment
Guest
Sunday, 05 December 2021

Captcha Image