Skip to main content

​
Overview

Calculated columns use JavaScript formulas to derive values dynamically. Common examples include:
  • RPN (Risk Priority Number): Severity × Occurrence × Detection
  • Risk scores: Probability × Impact
  • Aggregated values: Sum, average, or collect data from linked items
  • Conditional values: IF-style logic based on other field values

​
Method 1: Inline Formula Configuration

This method embeds the formula directly in risksheet.json. Best for simple calculations.

​
Step 1: Define the Formula

Add your formula to the formulas section: 
"formulas": {
  "commonRpn": "function(info){ var value = info.item['sev']*info.item['occ']*info.item['det']; return value ? value : null; }"
}

​
Step 2: Reference the Formula in Column Configuration

Add a column that uses this formula: 
{
  "headerGroup": "Risk Ranking",
  "headerCss": "headRanking",
  "header": "RPN",
  "bindings": "rpn",
  "type": "int",
  "formula": "commonRpn",
  "width": 60,
  "filterable": true,
  "level": 2,
  "id": "rpn"
}
Set "type": "int" to automatically round calculated values to whole numbers. Use "type": "float" for decimal precision.

​
Method 2: Top Panel Formula Configuration

Recommended for complex formulas or multiple lengthy calculations. This keeps risksheet.json clean and makes formulas easier to maintain.

​
Step 1: Define Formula Stub

In risksheet.json, reference a function defined elsewhere: 
"formulas": {
  "commonRpn": "(info) => { return getCommonRpn(info); }"
}

​
Step 2: Add Column Configuration

Configure the column exactly as in Method 1: 
{
  "header": "RPN",
  "bindings": "rpn",
  "type": "int",
  "formula": "commonRpn",
  "id": "rpn"
}

​
Step 3: Implement Formula in Top Panel

Open Menu → Customize Top Panel and add your JavaScript function: 
<script type="text/javascript">
  function getCommonRpn(info) {
    var sev = info.item['sev'];
    var occ = info.item['occ'];
    var det = info.item['det'];
    var value = sev * occ * det;
    return value ? value : null;
  }
</script>

​
Formula API Reference

The info parameter provides access to:
PropertyDescription
info.itemData object containing all column values for the current row
info.item['columnId']Access specific column value using its id
info.cellHTML DOM element of the current cell (rarely used in formulas)
info.valueCurrent value of the cell (for cell decorators)

​
Accessing Column Values

Use the column id to reference values: 
var severity = info.item['sev'];          // Column with id="sev"
var occurrence = info.item['occ'];        // Column with id="occ"
var detection = info.item['det'];         // Column with id="det"
For enumeration columns, info.item['columnId'] returns the enum option ID (e.g., 'acceptable', 'riskMitigation'), not the display name. Use the ID in your formula logic.

​
Advanced Formula Examples

​
Conditional Risk Calculation

"conditionalRisk": "function(info) {
  var prob = info.item['probability'];
  var impact = info.item['impact'];
  if (prob === 'high' && impact === 'high') {
    return 'Critical';
  } else if (prob === 'high' || impact === 'high') {
    return 'High';
  } else {
    return 'Medium';
  }
}"

​
Collecting Unique Values from Child Items

For aggregating data across hierarchy levels: 
function collectUniqueProcesses(info) {
  var childRows = risksheet.ds.getMasterRowsByColumnValue('parentId', info.item['id']);
  var processes = new Set();
  childRows.forEach(function(row) {
    if (row.processStep) {
      processes.add(row.processStep);
    }
  });
  return Array.from(processes).join(', ');
}
The risksheet.ds.getMasterRowsByColumnValue() API is available in version 24.9.1+. Use it to query related work items and aggregate their data.

​
Persisting Calculated Values to Polarion

By default, calculated values exist only in the RISKSHEET UI. To save them to Polarion work items:

​
Step 1: Create Custom Field

  1. Go to Administration → Custom Fields → Work Items
  2. Select the relevant work item type (e.g., Risk)
  3. Add a new field:
    • ID: rpn (must match column bindings)
    • Name: Risk Priority Number
    • Type: Integer (or appropriate type)
  4. Mark the field as read-only in Polarion to prevent direct editing

​
Step 2: Enable Writing to Field

Add "readOnly": false to your column configuration: 
{
  "bindings": "rpn",
  "formula": "commonRpn",
  "readOnly": false
}
Now the calculated value will be saved to Polarion when you save the RISKSHEET.
Formulas do not execute when the column is hidden from view. If you create work items with a hidden calculated column (e.g., auto-generated title), the formula will not populate the value. Workaround: Use the Check Stored Formulas feature (available in version 24.5.1+) from the menu to synchronize formula values after creation.

​
Data Synchronization (Version 24.5.1+)

Starting with version 24.5.1, RISKSHEET includes improved data synchronization for formula columns:
  • Formula values persist correctly between Polarion and RISKSHEET
  • Excel exports include calculated column values consistently
  • Imported data without saved values will display correctly after running Check Stored Formulas
diagram

​
Verification

You should now see:
  • The calculated column displaying computed values automatically
  • Values updating in real-time as you edit source columns
  • Read-only cell styling (typically gray background) if formula column is not writable
  • If persistence is enabled: values saved to Polarion work items and visible in work item forms

​
Troubleshooting

IssueSolution
Formula returns NaNCheck that source columns contain numeric values, not strings or enums
Values not updatingEnsure source column IDs match exactly (case-sensitive)
Export missing valuesUpgrade to version 24.5.1+ or use Check Stored Formulas
Hidden column not calculatingMake column visible during item creation, or use Check Stored Formulas afterward

​
See Also

KB ArticlesSupport TicketsSource Code
  • AppConfig.ts
  • AppConfigHelper.ts
  • PolarionAppConfigManager.java
  • risksheet.json
  • ConfigureColumnsCommand.ts