Configure calculated column (formula)

Modified on Thu, 27 Mar at 5:09 PM

You can automatically calculate values with formulas based on the data from other columns. This allows for dynamic and efficient data management, reducing manual input and ensuring consistency in calculations.

TABLE OF CONTENTS

Configuration in the risksheet.json
Configuration in risksheetTopPanel.vm
How to store the calculated value in the Work Item

An example of this in practice is the RPN (Risk Priority Number) column, where the value is calculated by multiplying the values from the Severity, Occurrence, and Detection columns together.

RPN = Severity * Occurence * Detection

Below you will find two different ways how to configure the calculated column.

Configuration in the risksheet.json

Setup

1. Define the formula in configuration

With this method, you will have to write the calculation code directly in the risksheet.json in the formulas parameter.

...
"formulas":{
  ...
  "commonRpn":"function(p){\n var value = p.item['sev']*p.item['occ']*p.item['det'];\n return value?value:null;}",
  ...
},
...

In the example above, you can see that the formula function takes as an input one parameter (here it is called p).

This parameter exposes:

p.item - a data array, that holds the values of rows. So in your formula, you can access the row (item) values by calling p.item[column.id]
p.cell - a HTML DOM element, usually not used in formulas, but this is used typically in cellDecorators

2. point a column to a formula definition

"columns": [
  ...
    {
      "headerGroup":"Set ranking",
      "headerCss":"headSetRanking",      
      "header": "RPN",
      "filterable":true,      
      "bindings": "rpn",
      "type": "int",
      "cellRenderer": "rpn",
      "width": 60,
      "level": 2,
      "formula":"commonRpn"
    },
  ...
  ]
....

This column configuration shows:

commonRpn is a function defined in the section called: formulas
You see that column.formula is pointing to formula ID: commonRpn
The function is defined in the JavaScript language and needs to return a value (can be a number, string, ...)

Tip: Set your column to Integer to round up the result.

Configuration in risksheetTopPanel.vm

It is recommended to use this method for Risksheets with many (or lengthy) formulas. This method helps us include multiple formulas while keeping the configuration clean and uncluttered.

Setup

1. Define the formula in configuration

With this method, the calculation will be defined in risksheetTopPanel.vm.

...
"formulas":{
  ...
"commonRpn":"(info) => { return getCommonRpn(info); }"
 ...
},
...

The value will be returned with the getCommonRpn function, the JavaScript code for this is stored in risksheetTopPanel.vm file (step 3).

2. Point a column to a formula definition

This step is the same as in the previous method, add "formula":"commonRpn" to your calculated column.

"columns": [
  ...
    {
      "headerGroup":"Set ranking",
      "headerCss":"headSetRanking",      
      "header": "RPN",
      "filterable":true,      
      "bindings": "rpn",
      "type": "int",
      "cellRenderer": "rpn",
      "width": 60,
      "level": 2,
      "formula":"commonRpn"
    },
  ...
  ]
....

3. Configure the risksheetTopPanel.vm file

Download the file from document attachments > Add the following code snippet to the file > Upload it again to the document > Overwrite and save

<script type="text/javascript">
    function getCommonRpn(info) {
        var occ = info.item['sev'];
        var occ = info.item['occ'];
        var det = info.item['det'];
        var value = sev * occ *det;
        return value ? value : null;
    }
</script>

How to store the calculated value in the Work Item

By default, the value is not stored in Polarion, but it can be saved by adding a custom field in Administration.

1. Go to Custom Fields in Work Items, and choose the Work Item. In this case, we will choose Risk.

2. Add ID and name it appropriately. We will go with "rpn" and Risk Priority.

3. It is also recommended to make the Risk Priority field read-only in Administration.

4. Add "readOnly":false" to the column configuration.

...
"columns": [
  ...
    {
  ...
      "bindings": "rpn",
      "readOnly":false,
      "formula":"commonRpn"
    },
  ...
  ]

....

You will see this custom field in your Work Item on Polarion if you have done all the previous steps correctly.

For any assistance, please don’t hesitate to reach out by submitting a ticket here.