> ## Documentation Index
> Fetch the complete documentation index at: https://learn.nextedy.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Configure Calculated Columns

> Add formula-driven columns to your Nextedy RISKSHEET that automatically compute values from other cells — for example, Risk Priority Numbers (RPN), weighted scores, or aggregated data from linked items.

export const LastReviewed = ({date}) => {
  if (!date) return null;
  const formatted = new Date(`${date}T00:00:00Z`).toLocaleDateString("en-US", {
    year: "numeric",
    month: "long",
    day: "numeric",
    timeZone: "UTC"
  });
  return <p className="mt-10 text-sm text-gray-400 dark:text-zinc-500 not-prose">
      Last reviewed on {formatted}
    </p>;
};

## Prerequisites

* Access to the sheet configuration (the configuration editor at **Menu > Configuration > Edit Risksheet Configuration** supports YAML editing since v25.5.0)
* Understanding of which Polarion fields hold the source data for your calculations (the `bindings` of the source columns)
* Familiarity with JavaScript expression syntax

## Two Ways to Define Formulas

Risksheet supports two complementary places to write formula functions:

| Method                                 | Where                                             | When to use                                                                                                          |
| -------------------------------------- | ------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------- |
| **Method 1 — Sheet configuration**     | The `formulas` section of the sheet configuration | Simple, self-contained calculations (RPN = S \* O \* D, conditional thresholds, value combinations)                  |
| **Method 2 — Top panel configuration** | A `<script>` block inside `risksheetTopPanel.vm`  | Complex logic, risk matrices, shared functions reused by several columns, code that benefits from a code review gate |

For regulated industries (medical devices, automotive safety, aerospace), the recommended pattern is to keep the sheet configuration declarative (simple wrappers) and externalize complex calculation logic to the top panel configuration. The wrapper in the sheet configuration calls a function defined in the top panel — this keeps the auditable structure of the grid separate from custom JavaScript.

<Frame>
  <img src="https://mintcdn.com/none-17b4493f/AIn5YJnEEQyTvSQd/risksheet/images/48001172968/1.png?fit=max&auto=format&n=AIn5YJnEEQyTvSQd&q=85&s=e6ea8a28f9cdee5af193f7486c942acf" alt="A calculated RPN column computing Risk Priority Number as Severity times Occurrence times Detection" width="644" height="706" data-path="risksheet/images/48001172968/1.png" />
</Frame>

<Steps>
  <Step title="Define the Formula">
    Open your sheet configuration and add a named formula to the top-level `formulas` section. Each formula is a JavaScript function that receives an `info` parameter containing the current row data.

    ```yaml theme={null}
    formulas:
      commonRpn: "function(info){ var value = info.item['occ']*info.item['det']*info.item['sev']; return value?value:null; }"
    ```

    The `info` object provides access to:

    | Property            | Description                                                          |
    | ------------------- | -------------------------------------------------------------------- |
    | `info.item`         | The current row's data object, keyed by column `id` / binding        |
    | `info.item['<id>']` | Value of any column in the same row (use the column `id` as the key) |

    <Tip title="Return null for empty values">
      Always return `null` when source values are missing or zero to prevent displaying misleading results. The pattern `return value ? value : null;` ensures blank cells instead of zeros when inputs are incomplete.
    </Tip>

    <Note title="Method 2: Defining formulas in the top panel">
      For complex calculation logic you can define the function body in `risksheetTopPanel.vm` and call it from a thin wrapper in the sheet configuration:

      ```yaml theme={null}
      formulas:
        commonRpn: "(info) => { return getCommonRpn(info); }"
      ```

      The actual `getCommonRpn(info)` function is then defined inside a `<script>` block in the top panel template. This pattern is the **recommended approach** for regulated environments because the sheet configuration stays declarative and auditable.
    </Note>
  </Step>

  <Step title="Create the Calculated Column">
    Add a column entry in the `columns` section and reference your named formula by setting the `formula` property to the formula name.

    ```yaml theme={null}
    columns:
      - id: rpn
        header: RPN
        formula: commonRpn
        width: 60
    ```

    Key column properties for calculated columns:

    | Property       | Default                    | Description                                                                                                                                    |
    | -------------- | -------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- |
    | `formula`      | none                       | Name of the formula defined in the `formulas` section                                                                                          |
    | `readOnly`     | `true` for formula columns | Formula columns are automatically read-only. Set `readOnly: false` to also enable persisting the calculated value back to Polarion (see below) |
    | `type`         | auto-detected              | Data type inferred from `bindings`; override explicitly if needed                                                                              |
    | `width`        | auto                       | Column width in pixels                                                                                                                         |
    | `header`       | column `id`                | Display text shown in the column header                                                                                                        |
    | `level`        | (none for task columns)    | 1-indexed visual hierarchy level for cell merging                                                                                              |
    | `filterable`   | `true`                     | Controls whether users can filter by this column's values                                                                                      |
    | `cellRenderer` | none                       | Optional reference to a function in `cellDecorators` for custom cell rendering                                                                 |

    <Note title="Formula values are not stored in Polarion by default">
      By default, formula columns are **calculated on the fly** every time the grid renders — the computed value is **not** written back to a Polarion custom field. The result lives only in the visible cell.

      If you need the value to be **stored** on the underlying work item (for example, to query it through Polarion Lucene, export it through OData, or use it on dashboards outside Risksheet), set `readOnly: false` and provide a `bindings` to a custom field that will hold the value:

      ```yaml theme={null}
      columns:
        - id: rpn
          header: RPN
          formula: commonRpn
          bindings: rpnStored        # custom field that receives the result
          readOnly: false
          width: 60
      ```

      With `readOnly: false`, Risksheet writes the formula result into the bound custom field whenever the row is saved.

      <Frame>
        <img src="https://mintcdn.com/none-17b4493f/AIn5YJnEEQyTvSQd/risksheet/images/48001172968/2.png?fit=max&auto=format&n=AIn5YJnEEQyTvSQd&q=85&s=31f08c55b31c497ee454b138afaabf84" alt="Adding readOnly false and formula commonRpn so the calculated value is stored as a custom field on the Polarion Work Item" width="317" height="80" data-path="risksheet/images/48001172968/2.png" />
      </Frame>
    </Note>
  </Step>

  <Step title="Build Common Formula Patterns">
    ### RPN Calculation (Severity x Occurrence x Detection)

    The standard Risk Priority Number formula multiplies three rating values. Most FMEA workflows use both an initial RPN and a revised RPN (after mitigations) — but the same shape applies to other methodologies that combine numeric ratings.

    ```yaml theme={null}
    formulas:
      commonRpn: "function(info){ var value = info.item['occ']*info.item['det']*info.item['sev']; return value?value:null; }"
      commonRpnNew: "function(info){ var value = info.item['occNew']*info.item['detNew']*info.item['sevNew']; return value?value:null; }"
    ```

    Then add two columns referencing these formulas:

    ```yaml theme={null}
    columns:
      - id: rpn
        header: RPN (Initial)
        formula: commonRpn
        width: 70
        headerGroup: Initial Rating
      - id: rpnNew
        header: RPN (Revised)
        formula: commonRpnNew
        width: 70
        headerGroup: Revised Rating
    ```

    ### Combining Fields from Linked Items

    You can build formulas that concatenate or transform values from linked upstream items. For example, to combine an ID and title into a single display value:

    ```yaml theme={null}
    formulas:
      combinedIdTitle: "function(info){ var id = info.item['linkedItemId']; var title = info.item['linkedItemTitle']; return id && title ? id + ' - ' + title : (id || title || null); }"
    ```

    <Tip title="Cross-row data aggregation with getMasterRowsByColumnValue">
      Since version 24.9.1, the `risksheet.ds.getMasterRowsByColumnValue()` function enables aggregating data from downstream risk items into a parent row. This is useful when a process step needs to summarize characteristics from its child risk items — for example, collecting unique enum values from multiple risks or summing numeric fields across related items. Formulas that use this function can also live in `risksheetTopPanel.vm` for easier sharing.
    </Tip>

    ### Conditional Value Formula

    Return different values based on cell data for risk classification:

    ```yaml theme={null}
    formulas:
      riskLevel: "function(info){ var rpn = info.item['rpn']; if(!rpn) return null; if(rpn <= 150) return 'Low'; if(rpn <= 250) return 'Medium'; return 'High'; }"
    ```

    ### Conditional Editability via Cell Decorators

    You can use `cellDecorators` together with `info.item.systemReadOnlyFields` to conditionally lock cells based on data values. The `systemReadOnlyFields` value is a pipe-delimited string — appending `|fieldId|` makes that cell read-only for the current row:

    ```yaml theme={null}
    cellDecorators:
      conditionalReadonly: "(info) => { if(info.item['status'] === 'approved'){ info.item.systemReadOnlyFields += '|description|'; info.cell.addClass('rs-readonly'); } }"
    ```

    <Warning title="Conditional editability requires careful testing">
      Per-row read-only behavior interacts with both column-level and item-level permission settings. Test the `systemReadOnlyFields` approach thoroughly in your environment before relying on it for compliance-critical workflows.
    </Warning>
  </Step>

  <Step title="Add Conditional Formatting">
    Pair your calculated columns with `cellDecorators` and `styles` for visual risk indicators. Cell decorators **must** use `toggleClass` (not inline style assignment) — Risksheet reuses cell DOM elements as the user scrolls, and inline styles will bleed between rows.

    ```yaml theme={null}
    cellDecorators:
      rpn: "function(info){ var val = info.value; $(info.cell).toggleClass('boldCol', true); $(info.cell).toggleClass('rpn1', val>0 && val <= 150); $(info.cell).toggleClass('rpn2', val > 150 && val <= 250); $(info.cell).toggleClass('rpn3', val > 250); }"

    styles:
      ".boldCol": "{font-weight:600;}"
      ".rpn1": "{background-color: #eaf5e9 !important; color: #1d5f20 !important;}"
      ".rpn2": "{background-color: #fff3d2 !important; color: #735602 !important;}"
      ".rpn3": "{background-color: #f8eae7 !important; color: #ab1c00 !important;}"
    ```

    The decorator function receives `info.value` (the computed formula result) and `info.cell` (the DOM element), so it can toggle CSS classes based on thresholds:

    | RPN Range  | Style Class | Appearance                      |
    | ---------- | ----------- | ------------------------------- |
    | 1 -- 150   | `rpn1`      | Green background (low risk)     |
    | 151 -- 250 | `rpn2`      | Yellow background (medium risk) |
    | > 250      | `rpn3`      | Red background (high risk)      |

    <Note title="Thresholds vary per deployment">
      These thresholds are deployment-specific; adjust them to your project's risk-acceptance criteria. Some deployments use simple low/medium/high bands (such as 4 / 8) instead of RPN cutoffs. Risksheet does not impose default RPN thresholds — choose values that match your organization's risk policy.
    </Note>

    <Frame>
      <img src="https://mintcdn.com/none-17b4493f/-Icoak49xQVOW38R/risksheet/diagrams/guides/columns/calculated-columns/diagram-1.svg?fit=max&auto=format&n=-Icoak49xQVOW38R&q=85&s=32d4ad1d9ac3a9fc2bcc6c8b37db4478" alt="diagram" style={{ maxWidth: "620px", width: "100%" }} width="620" height="200" data-path="risksheet/diagrams/guides/columns/calculated-columns/diagram-1.svg" />
    </Frame>
  </Step>

  <Step title="Apply Formula-Based Styling to Row Headers">
    You can also drive row header coloring from a formula value through the `headers.rowHeader.renderer` property. This colors the entire row header based on a data value, giving an at-a-glance risk indicator:

    ```yaml theme={null}
    headers:
      rowHeader:
        renderer: rowHeaderRpnNew

    cellDecorators:
      rowHeaderRpnNew: "function(info){ var val = info.item['rpnNew']; $(info.cell).toggleClass('rpn1', val>0 && val <= 150); $(info.cell).toggleClass('rpn2', val>0 && val > 150 && val <= 250); $(info.cell).toggleClass('rpn3', val>0 && val > 250); }"
    ```

    When scanning a large risk analysis table, you can immediately spot high-risk rows by their colored header cells.

    <Warning title="Hidden formula columns do not execute (verify behavior)">
      If a formula column is hidden through column visibility settings or a saved view, its formula does **not** execute for that view. Any cell decorator or other column that depends on the hidden formula's value can show stale data. If a `title` column uses a formula and is hidden during item creation, the resulting Polarion work item may store an incorrect title. Keep formula columns visible during item creation, or use the **Check stored formulas** feature (introduced in v24.5.1) to reconcile stored values afterwards.

      *This detail (non-execution of hidden formula columns and the "Check stored formulas" feature in v24.5.1) is product-team knowledge that has no published Support Portal article — verify against your Risksheet version before relying on it for compliance-critical workflows.*
    </Warning>
  </Step>

  <Step title="Verify Your Configuration">
    1. Save your sheet configuration changes
    2. Refresh the Risksheet page in your browser
    3. The calculated column displays computed values automatically
    4. The column is read-only — clicking a formula cell does not open an editor (unless you set `readOnly: false` to persist results)
    5. Change a source value (for example, update a severity rating) and confirm the formula recalculates immediately
    6. Confirm cell decorators apply the correct color coding based on the formula result

    <Tip title="Use Check stored formulas for bulk reconciliation">
      After adding or modifying formulas in columns that persist their value (`readOnly: false`), use the **Check stored formulas** feature to scan all rows and update any stored values that differ from the current formula result. This matters after changing formula logic on an existing Risksheet with historical data: the feature detects differences between the current formula result and the stored value, and marks affected items for update.
    </Tip>
  </Step>
</Steps>

## Complete Example

A full sheet configuration snippet for an FMEA-style table with initial and revised RPN calculations, conditional formatting, and row header coloring:

```yaml theme={null}
formulas:
  commonRpn: "function(info){ var value = info.item['occ']*info.item['det']*info.item['sev']; return value?value:null; }"
  commonRpnNew: "function(info){ var value = info.item['occNew']*info.item['detNew']*info.item['sevNew']; return value?value:null; }"

columns:
  - id: sev
    header: Severity
    type: rating:severity
    bindings: sev
    width: 80
    headerGroup: Initial Rating
  - id: occ
    header: Occurrence
    type: rating:occurrence
    bindings: occ
    width: 80
    headerGroup: Initial Rating
  - id: det
    header: Detection
    type: rating:detection
    bindings: det
    width: 80
    headerGroup: Initial Rating
  - id: rpn
    header: RPN
    formula: commonRpn
    width: 60
    headerGroup: Initial Rating
  - id: sevNew
    header: Severity
    type: rating:severity
    bindings: sevNew
    width: 80
    headerGroup: Revised Rating
  - id: occNew
    header: Occurrence
    type: rating:occurrence
    bindings: occNew
    width: 80
    headerGroup: Revised Rating
  - id: detNew
    header: Detection
    type: rating:detection
    bindings: detNew
    width: 80
    headerGroup: Revised Rating
  - id: rpnNew
    header: RPN
    formula: commonRpnNew
    width: 60
    headerGroup: Revised Rating

cellDecorators:
  rpn: "function(info){ var val = info.value; $(info.cell).toggleClass('boldCol', true); $(info.cell).toggleClass('rpn1', val>0 && val <= 150); $(info.cell).toggleClass('rpn2', val > 150 && val <= 250); $(info.cell).toggleClass('rpn3', val > 250); }"
  rpnNew: "function(info){ var val = info.value; $(info.cell).toggleClass('boldCol', true); $(info.cell).toggleClass('rpn1', val>0 && val <= 150); $(info.cell).toggleClass('rpn2', val > 150 && val <= 250); $(info.cell).toggleClass('rpn3', val > 250); }"
  rowHeaderRpnNew: "function(info){ var val = info.item['rpnNew']; $(info.cell).toggleClass('rpn1', val>0 && val <= 150); $(info.cell).toggleClass('rpn2', val>0 && val > 150 && val <= 250); $(info.cell).toggleClass('rpn3', val>0 && val > 250); }"

styles:
  ".boldCol": "{font-weight:600;}"
  ".rpn1": "{background-color: #eaf5e9 !important; color: #1d5f20 !important;}"
  ".rpn2": "{background-color: #fff3d2 !important; color: #735602 !important;}"
  ".rpn3": "{background-color: #f8eae7 !important; color: #ab1c00 !important;}"

headers:
  rowHeader:
    renderer: rowHeaderRpnNew
```

## See Also

* [Configure Downstream Traceability Columns](/risksheet/guides/columns/downstream-traceability) -- set up source columns that feed into calculated formulas
* [Apply Conditional Formatting](/risksheet/guides/styling/conditional-formatting) -- cell decorators and styles in depth
* [Configure Cell Styles](/risksheet/guides/styling/cell-styles) -- define custom CSS classes for calculated columns
* [Work with Formulas and Hidden Columns](/risksheet/guides/advanced/formulas-hidden-columns) -- the interaction between column visibility and formula execution
* [Configure Row Header Styles](/risksheet/guides/styling/row-header-styles) -- formula-based row header coloring
* [Add a Basic Column](/risksheet/guides/columns/add-basic-column) -- general column setup fundamentals

<LastReviewed date="2026-06-24" />