AdapTable Version:

Framework:

Configuring Calculated Columns

Summary

Calculated Columns can be provided by developers through Calculated Column Initial State
The definition includes an Id, the Column's Expression and any associated behaviour

Defining Calculated Columns

Calculated Columns can be defined by developers via Calculated Column Initial Adaptable State.

Each CalculatedTextColumn object contains 3 main sets of properties to:

uniquely identify and name the Calculated Column
provide the Expression which is to be evaluated
describe the behaviour of the Column

Deep Dive

Anatomy of a Calculated Column

The CalculatedColumn object is defined as follows:

Property	Description
CalculatedColumnSettings	Additional optional properties for Column (e.g. filterable, resizable)
ColumnId	Name of Calculated Column
FriendlyName	Name to be used in Column Header; if blank `ColumnId` is used
Query	Scalar/AggregatedScalar Query used by AdapTableQL to evaluate Column's value
IsReadOnly	Sets Entity to ReadOnly (overwriting a Strategy Entitlement of 'Full')

Calculated Column Settings

The CalculatedColumnSettings object inherits from the base SpecialColumnSettings which is defined as follows:

Caution

DataType is the only mandatory property

Property	Description	Default
Aggregatable	Whether Column can be used in an aggregation when grouping	false
ColumnTypes	Custom column types added to AG Grid Column Types when object is created
DataType	Expression's return value DataType, only mandatory property
Filterable	Whether Column is filterable	false
Groupable	Whether Column can be grouped	false
HeaderToolTip	Tooltip to show in the Column Header (not cells)
Pivotable	Whether Column can be used when grid is in pivot mode	false
Resizable	Whether Column can be resized (by dragging column header edges)	false
Sortable	Whether Column is sortable	false
SuppressMenu	Whether if no menu should be shown for this Column header.	false
SuppressMovable	Whether if this Column should be movable via dragging	false
Width	Preferred (pixel) Column Width; if unset, calculated dynamically by AG Grid

There is one additional, optional property added for Calculated Column:

Property	Description	Default
ShowToolTip	Show underlying Expression as ToolTip when hovering over a cell	false

Developer Guide

Defining a Calculated Column

There are 5 properties that you need to provide when defining a Calculated Column:

// Provide 3 Calculated Columns
// 1. Total PRs - a Standard Expression
// 2. Issues Open/Total Ratio - Aggregated Expression
// 3. Cumulated Stars Count - Cumulative Expression
CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'total_pr_count',
      FriendlyName: 'Total PRs',
      Query: {
        ScalarExpression: 
        '[open_pr_count] + [closed_pr_count]',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
        Filterable: true,
      },
    },
    {
      ColumnId: 'open-total-issue-ratio',
      FriendlyName: 'Issues Open/Total Ratio',
      Query: {
        ScalarExpression:
        '[open_issues_count] / ([open_issues_count] +[closed_issues_count])',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
        Sortable: true,
      },
    },
    {
      ColumnId: 'cumulated-stars-count',
      FriendlyName: 'Cumulated Stars Count',
      Query: {
        AggregatedScalarExpression:
          'CUMUL( SUM([github_stars]), OVER([created_at]) )',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
  ],
},

Id for the Calculated Column

This ColumnId value is used to reference the Column in AdapTable State and in other objects (e.g. Layouts)

Column Friendly Name (if required)

This value is used to refer to the Column in AdapTable UI It only needs to be provided if the ColumnId value is unsuitable

The Calculated Column Expression

This is the Expression which is evaluated by AdapTableQL each time the cell is rendered. It is wrapped inside a Query property and can be one of 2 types:

ScalarExpression (Standard) - used in 1st example
AggregatedScalarExpression - used in 3rd example

Data Type

The Data Type of the Calculated Column should be provided in CalculatedColumnSettings. It is a Cell Data Type value and the object's only mandatory property. Common values are number, text or date.

Calculated Column Settings (Optional)

Additional Settings for the Calculated Column (other than mandatory DataType) can be provided including:

Filterable
Pivotable
Sortable
Groupable

Hint

Disallow filtering on all Calculated Columns by setting enableFilterOnSpecialColumns to false in Column Filter Options

AG Grid Definitions

Calculated columns are typically not provided in the ColDefs property in AG Grid GridOptions.

Instead, the definition provided in Initial State suffices for AdapTable to be able to create the associated AG Grid column automatically.

However sometimes a developer might want to add an AG Grid element to the column.

For instance a tooltip might be needed, or there might be a requirement to put the Calculated Column inside a Column Group (which is an AG Grid feature).

This is possible: a column can be provided in AG Grid ColDefs but by specifying a Column Type of calculatedColumn, AdapTable will automatically wire it up with an associated Calculated Column definition.

Note

Set the Column Type to be calculatedColumn
The ColId in the Column Definition and ColumnId in the Calculated Column definition must be the same value

Calculated Columns: Defining in AG Grid

Fork Fork

This demo contains an AG Grid Column Group - Github Averages
The group contains 2 Calculated Columns Github Avg By Language and Github Avg By Licence
Both the Column Group and the 2 containing Columns were defined in AG Grid Column Defs
The 2 columns in the Group were given AG Grid Header and Cell ToolTips
They were also given a type of calculatedColumn which is what allowed AdapTable to wire everything together

Try It Out

Hover over a cell in the Calculated Column and see the Tooltip which AG Grid provides

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Defining Calculated Columns in AG Grid',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'FormatColumn', 'SettingsPanel'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'github_stars',
            'license',
            'language',
            'starsAvgByLanguage',
            'starsAvgByLicence',
            'github_watchers',
          ],
          Name: 'Standard Layout',
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'starsAvgByLanguage',
          Query: {
            AggregatedScalarExpression:
              'AVG([github_stars] ,GROUP_BY([language] ) ) ',
          },
          FriendlyName: 'Github Avg By Language',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'starsAvgByLicence',
          Query: {
            AggregatedScalarExpression:
              'AVG([github_stars] ,GROUP_BY([license] ) ) ',
          },
          FriendlyName: 'Github Avg By Licence',
          CalculatedColumnSettings: {DataType: 'number'},
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-starsAvgByLanguage',
          Scope: {
            ColumnIds: ['starsAvgByLanguage', 'starsAvgByLicence'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
      ],
    },
  },
};

Find Out More

See Adding Column Types for Special Columns for more details and a demo

Referencing Calculated Columns in AG Grid

(Recorded with AdapTable v14.0)

PreviousReferencing Calculated Columns NextTechnical Reference

Framework: