Tracing Calculated Columns Formulas

Summary

AdapTable provides 2 UI components to allow run-time users to follow (and edit) a Calculated Column's formula
- Formula Trace shows - Excel-style - how a Calculated Column value is derived
- The Compact Expression Editor lets the Expression be edited inline

Calculated Columns are typically defined at design-time by developers.

But AdapTable also provides a rich run-time UI so that users can create, manage and - crucially - understand Calculated Columns.

Formula Trace

AdapTable includes an Excel-style Formula Trace which lets run-time users see - and inline edit - how a Calculated Column value is derived.

There are 2 ways to open Formula Trace for a Calculated Column cell:

Double-click the cell
Select the Show Formula item in the cell's Context Menu

Note

Both triggers are enabled by default, but each can be turned off - see Formula Trace Options below

When opened, Formula Trace displays a popup - anchored to the cell - containing the Column's Expression with every referenced Column shown in its own colour.

At the same time, AdapTable highlights the referenced data in the Grid using the same colours (in the manner of Excel):

for Standard Calculated Columns the referenced cells in the row are outlined and tinted
for Aggregated (and other multi-row) Calculated Columns the referenced Column headers are highlighted

Hint

For Standard Calculated Columns the popup also shows each referenced Column's name above its actual cell value for that row, followed by the computed result (e.g. = 42) - so you can see the calculation "worked through" for the current row

Editing a Formula

The Formula Trace popup contains an Edit button which opens the Compact Expression Editor.

This is a small, moveable and resizable version of the full Expression Editor, giving quick access to the Expression without opening the full Wizard.

It provides the same power as the full Editor, including:

the Functions dropdown listing all available Expression Functions
live validation
colour-coded Column references (matching the Formula Trace highlights)

Edits are applied live as you type - the Calculated Column re-evaluates immediately.

Note

An invalid Expression will show #INVALID in the cells until it is corrected

The Compact Editor also provides 2 buttons:

Revert - restores the Expression to how it was when the Editor was opened
Open Full Editor - closes the Compact Editor and opens the full Calculated Column Wizard

Hint

The Compact Expression Editor is available for all 6 Calculated Column types: Standard, Aggregated, Cumulative, Quantile, Window and Observable

Showing Formula Trace via the API

Formula Trace can also be opened programmatically using the Calculated Column API:

showFormulaTrace(columnId, primaryKeyValue) - opens the Formula Trace popup for the given Calculated Column cell
hideFormulaTrace() - closes the popup (and clears the Grid highlights)

Using Calculated Columns: Formula Trace

Fork Fork

This demo contains 3 Calculated Columns:
- Total PRs (Standard) - sums the Open PRs and Closed PRs Columns
- Issues Open/Total Ratio (Standard) - divides Open Issues by total Issues
- Sum Pop by Lang (Aggregated) - sums Github Watchers grouped by Language
After 5 seconds the demo calls showFormulaTrace to open the Formula Trace popup for the first Total PRs cell (see onAdaptableReady.ts)

Try It Out

Wait for the Formula Trace popup to appear, or double-click any Total PRs cell yourself
Note how the two referenced cells (Open PRs, Closed PRs) are highlighted in matching colours
Click the Edit button in the popup and change the Expression in the Compact Editor - the Column updates live
Double-click a Sum Pop by Lang cell and note the referenced Column header is highlighted instead

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Using Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'open_pr_count',
            'closed_pr_count',
            'total_pr_count',
            'open_issues_count',
            'closed_issues_count',
            'open-total-issue-ratio',
            'language',
            'github_watchers',
            'sum-popularity-by-lang',
            'github_stars',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Total PRs',
          ColumnId: 'total_pr_count',
          Expression: '[open_pr_count] + [closed_pr_count]',
          CalculatedColumnSettings: {
            DataType: 'number',
            Filterable: true,
          },
        },
        {
          FriendlyName: 'Issues Open/Total Ratio',
          ColumnId: 'open-total-issue-ratio',
          Expression: '[open_issues_count] / ([open_issues_count] + [closed_issues_count])',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'sum-popularity-by-lang',
          FriendlyName: 'Sum Pop by Lang',
          Expression: 'SUM{[github_watchers], GROUP_BY([language])}',
          CalculatedColumnSettings: {
            DataType: 'number',
            Groupable: true,
          },
        },
      ],
    },
  },
};

Read-only

Loading demo…

A Calculated Column can also show its formula as a tooltip when the user hovers over a cell.

For per-row Calculated Columns (e.g. Standard) the tooltip shows the formula and, underneath, the same formula with this row's values filled in, followed by the result.

For multi-row Calculated Columns (e.g. Aggregated) the tooltip shows the formula.

Turn it on by setting ShowToolTip in the Calculated Column's CalculatedColumnSettings (or checking Show Expression as Cell Tooltip in the Calculated Column Wizard):

calculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'TotalPRs',
      Expression: '[open_pr_count] + [closed_pr_count]',
      CalculatedColumnSettings: {
        DataType: 'number',
        ShowToolTip: true,
      },
    },
  ],
},

Note

ShowToolTip is false by default

Configuring Formula Trace

Formula Trace is configured via the formulaTrace property in Calculated Column Options.

There are 2 properties available to set the formula trace behaviour:

enableDoubleClick

Default: true

Boolean

Whether double-clicking a Calculated Column cell opens Formula Trace

By default, users can open Formula Trace by double-clicking any Calculated Column cell.

Set this to false to disable the double-click trigger (e.g. if double-click is used for something else in your application).

  calculatedColumnOptions: {
    formulaTrace: {
      enableDoubleClick: false,
    },
  },

showContextMenuItem

Default: true

Boolean

Whether the "Show Formula" item appears in the cell Context Menu

By default, a Show Formula item is added to the Context Menu for Calculated Column cells.

Set this to false to remove that Context Menu item.

  calculatedColumnOptions: {
    formulaTrace: {
      showContextMenuItem: false,
    },
  },

Tracing Calculated Columns Formulas

Summary

Formula Trace

Note

Hint

Editing a Formula

Note

Hint

Showing Formula Trace via the API

Cell Tooltip

Note

Configuring Formula Trace

enableDoubleClick

showContextMenuItem

AdapTable Resources