# Server-Side Row Model - Calculated Columns

Canonical page: https://www.adaptabletools.com/docs/dev-guide-row-models-server-calculated-columns

- Standard and Observable Calculated Columns evaluate on the client as each row renders
- Aggregated, Cumulative, Quantile and Window Calculated Columns need the calculation to be performed on the server

[Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) can work automatically when using AG Grid's Server-Side Row Model.

However this does depend on the complexity of the Calculation.

## Standard

Standard Calculated Columns operate as normal in the Server-Side Row Model.

This is because they use [Standard Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-standard/index.md) which operate on a **single** row and evaluate as the row is rendered

Adding, editing or deleting a Calculated Column keeps the rows already loaded from the server; the new values are evaluated as the rows render.

While a Column Filter or Grid Filter is active, a Calculated Column change reloads the rows from the server, as the filtered rows may change

**Example: SSRM - Standard Calculated Columns**

Server-Side Row Model: Standard Calculated Columns

- This demo includes 3 Calculated Columns - `Total`, `Most`, `Points`
- All are standard (i.e. single-row) Calculated Columns and all work out of the box when using Server-Side Row Model

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts
  gridOptions.ts
  index.ts             [template]
  onAdaptableReady.ts
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';

export const adaptableOptions: AdaptableOptions = {
  primaryKey: 'id',
  adaptableId: 'SSRM Calculated Columns',
  initialState: {
    Dashboard: {
      PinnedToolbars: ['Layout'],
    },
    Theme: {
      CurrentTheme: 'dark',
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'athlete',
            'gold',
            'silver',
            'bronze',
            'totalMedals',
            'mostMedals',
            'points',
            'country',
            'sport',
            'year',
          ],
          ColumnSizing: {
            athlete: {Width: 175},
            bronze: {Width: 100},
            country: {Width: 125},
            gold: {Width: 100},
            silver: {Width: 100},
            sport: {Width: 175},
            year: {Width: 115},
          },
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'totalMedals',
          Expression: '[gold] + [silver] + [bronze] ',
          CalculatedColumnSettings: {
            DataType: 'number',
            Filterable: true,
            Resizable: true,
            Sortable: true,
          },
          FriendlyName: 'Total',
        },
        {
          ColumnId: 'mostMedals',
          Expression: 'max([gold], [silver],[bronze]) = [gold] ? "Gold" : max([gold], [silver],[bronze]) = [silver] ? "Silver": "Bronze"',
          CalculatedColumnSettings: {
            DataType: 'text',
            Filterable: true,
            Resizable: true,
            Sortable: true,
          },
          FriendlyName: 'Most',
        },
        {
          ColumnId: 'points',
          Expression: '([gold] * 3) + ([silver] * 2) + ([bronze] * 1)',
          CalculatedColumnSettings: {
            DataType: 'number',
            Filterable: true,
            Resizable: true,
            Sortable: true,
          },
          FriendlyName: 'Points',
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

```ts
import {AdaptableReadyInfo, AdaptableApi} from '@adaptabletools/adaptable';
import {
  ColDef,
  GridApi,
  IServerSideDatasource,
  IServerSideGetRowsParams,
  IServerSideGetRowsRequest,
} from 'ag-grid-enterprise';

export const onAdaptableReady = ({
  adaptableApi,
  agGridApi,
}: AdaptableReadyInfo) => {
  agGridApi.setGridOption(
    'serverSideDatasource',
    createDataSource(adaptableApi)
  );
};

const API_BASE: string = process.env.NEXT_PUBLIC_ATHLETES_API_URL!;

// v24 Column Filters are Expressions. The athletes mock server evaluates
// both Grid Filter and Column Filters from `gridFilterAST`.
function rewriteAutoGroupColumn(
  node: unknown,
  groupedColumnId?: string
): unknown {
  if (!groupedColumnId || node == null || typeof node !== 'object') {
    return node;
  }
  if (Array.isArray(node)) {
    return node.map(item => rewriteAutoGroupColumn(item, groupedColumnId));
  }
  const astNode = node as {type?: string; args?: unknown[]};
  if (astNode.type === 'COL' && astNode.args?.[0] === 'ag-Grid-AutoColumn') {
    return {
      ...astNode,
      args: [groupedColumnId, ...astNode.args.slice(1)],
    };
  }
  if (!astNode.args) {
    return node;
  }
  return {
    ...astNode,
    args: astNode.args.map(arg =>
      rewriteAutoGroupColumn(arg, groupedColumnId)
    ),
  };
}

function appendExpressionAst(
  astNodes: unknown[],
  ast: unknown,
  groupedColumnId?: string
) {
  if (ast == null) {
    return;
  }
  const nodes = Array.isArray(ast) ? ast : [ast];
  for (const node of nodes) {
    astNodes.push(rewriteAutoGroupColumn(node, groupedColumnId));
  }
}

function toServerFilterFields(
  adaptableApi: AdaptableApi,
  groupedColumnId?: string
) {
  const filterState = adaptableApi.stateApi.getAdaptableFilterState();
  const astNodes: unknown[] = [];
  appendExpressionAst(astNodes, filterState.gridFilterAST, groupedColumnId);
  for (const columnFilter of filterState.columnFilters ?? []) {
    if (!columnFilter.Expression) {
      continue;
    }
    appendExpressionAst(
      astNodes,
      adaptableApi.expressionApi.getASTForExpression(columnFilter.Expression),
      groupedColumnId
    );
  }
  return {
    adaptableFilters: [],
    gridFilterAST: astNodes.length ? astNodes : undefined,
  };
}

function createDataSource(adaptableApi: AdaptableApi): IServerSideDatasource {
  return {
    getRows(params: IServerSideGetRowsParams) {
      const getRowsRequest: IServerSideGetRowsRequest = params.request;
      const adaptableSortState = adaptableApi.stateApi.getAdaptableSortState();
      const groupedColumn = getRowsRequest.rowGroupCols?.[0];
      const serverFilters = toServerFilterFields(
        adaptableApi,
        groupedColumn?.field ?? groupedColumn?.id
      );

      const sortModel = getRowsRequest.sortModel.map(sort => {
        const customSort = adaptableSortState.customSorts.find(
          customSort => customSort.ColumnId === sort.colId
        );
        if (customSort) {
          return {
            ...sort,
            sortedValues: customSort.SortedValues,
          };
        }
        return sort;
      });

      const request = {
        ...getRowsRequest,
        sortModel,
        ...serverFilters,
        includeSQL: true,
      };
      console.log('request to server', request);

      fetch(API_BASE, {
        method: 'post',
        body: JSON.stringify(request),
        headers: {'Content-Type': 'application/json; charset=utf-8'},
      })
        .then(httpResponse => httpResponse.json())
        .then(response => {
          params.success({
            rowData: response.rows,
            rowCount: response.lastRow ?? 0,
          });

          if (response.pivotFields?.length) {
            addPivotColumnDefs(response, params.api);
          } else {
            if (!adaptableApi.layoutApi.isCurrentLayoutPivot()) {
              if (params.api.getPivotResultColumns() != null) {
                params.api.setPivotResultColumns(null);
              }
            }
          }

          adaptableApi.systemStatusApi.setInfoSystemStatus(
            `SQL: ${response.sql.slice(0, 40)}`,
            response.sql
          );
        })
        .catch(error => {
          params.fail();
        });
    },
  };
}

function addPivotColumnDefs(response: any, agGridApi: GridApi) {
  const existingPivotColDefs = agGridApi.getPivotResultColumns();
  if (existingPivotColDefs && existingPivotColDefs.length > 0) {
    return;
  }

  const pivotColDefs: ColDef[] = response.pivotFields.map(function (
    field: any,
    index: number
  ) {
    const [_key, value] = Object.entries(field)[0];
    const valueStr = `${value}`;
    return {
      headerName: valueStr,
      field: valueStr,
      filter: false,
      colId: `${Date.now()}_${index}`,
      cellDataType: 'number',
    };
  });

  agGridApi.setPivotResultColumns(pivotColDefs);
}
```

**`src/gridOptions.ts`**

```ts
import {GridOptions, themeBalham} from 'ag-grid-enterprise';

import {columnDefs} from './columnDefs';

export const gridOptions: GridOptions = {
  theme: themeBalham,
  defaultColDef: {
    sortable: true,
    filter: true,
    floatingFilter: true,
    enableRowGroup: true,
  },
  rowModelType: 'serverSide',
  columnDefs: columnDefs,
  sideBar: ['adaptable', 'columns', 'filters'],
};
```

**`src/columnDefs.ts`**

```ts
import {ColDef, ColGroupDef} from 'ag-grid-enterprise';

export const columnDefs: (ColDef | ColGroupDef)[] = [
  {field: 'id', hide: true, cellDataType: 'text'},
  {
    field: 'athlete',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'country',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'sport',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'year',
    cellDataType: 'number',
    enableRowGroup: true,
    resizable: true,
  },
  {
    headerName: 'Medals',
    marryChildren: true,
    children: [
      {
        field: 'gold',
        aggFunc: 'sum',
        cellDataType: 'number',
        enableValue: true,
        resizable: true,
      },
      {
        field: 'silver',
        aggFunc: 'sum',
        cellDataType: 'number',
        enableValue: true,
        resizable: true,
      },
      {
        field: 'bronze',
        aggFunc: 'sum',
        cellDataType: 'number',
        enableValue: true,
        resizable: true,
      },
      {
        field: 'totalMedals',
        headerName: 'Total',
        type: ['calculatedColumn'],
        cellDataType: 'number',
      },
      {
        field: 'mostMedals',
        headerName: 'Most',
        type: ['calculatedColumn'],
        cellDataType: 'text',
      },
      {
        field: 'points',
        headerName: 'Points',
        type: ['calculatedColumn'],
        cellDataType: 'number',
      },
    ],
  },
];
```

## Observable

[Observable Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column-observable/index.md) also evaluate on the client.

They use [Observable Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-observable/index.md) which read **that row's change history** — and not other rows.

- AdapTable only records history for changes it has seen in this session, edits, `updateGridData`, `setCellValue` etc.
- Ticks that happen only on the server — or a row that is paged out and fetched again — have no history on the client

For server-authoritative history (e.g. how many times a price ticked in the last 5 minutes across the full dataset), evaluate the Expression on the server and return the value, as with Aggregated Columns.

**Example: SSRM - Observable Calculated Columns**

Server-Side Row Model: Observable Calculated Columns

- This demo ticks `Gold` on visible rows so AdapTable can record client-side change history
- It includes 3 [Observable Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column-observable/index.md) which evaluate as each row renders:
  - `Gold Ticks` — `CHANGE_COUNT{[gold], WITHIN(2m)}`
  - `Previous Gold` — `PREVIOUS{[gold]}`
  - `Net (30s)` — `NET_CHANGE{[gold], WITHIN(30s)}`
- `liveRefreshIntervalMs` is set so the windowed values keep updating as time passes

- Watch `Gold` flash as it ticks, and see `Gold Ticks` and `Previous Gold` update on the same row
- Scroll so new rows load — they start with empty history until they tick

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts
  gridOptions.ts
  index.ts             [template]
  onAdaptableReady.ts
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';

export const adaptableOptions: AdaptableOptions = {
  primaryKey: 'id',
  adaptableId: 'SSRM Observable Calculated Columns',
  expressionOptions: {
    liveRefreshIntervalMs: 1000,
  },
  initialState: {
    Dashboard: {
      PinnedToolbars: ['Layout'],
    },
    Theme: {
      CurrentTheme: 'dark',
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'athlete',
            'gold',
            'goldTicks',
            'prevGold',
            'goldNet',
            'country',
            'sport',
          ],
          ColumnSizing: {
            athlete: {Width: 175},
            gold: {Width: 90},
            goldTicks: {Width: 110},
            prevGold: {Width: 120},
            goldNet: {Width: 110},
            country: {Width: 125},
            sport: {Width: 175},
          },
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'goldTicks',
          FriendlyName: 'Gold Ticks',
          Type: 'Observable',
          Expression: 'CHANGE_COUNT{[gold], WITHIN(2m)}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'prevGold',
          FriendlyName: 'Previous Gold',
          Type: 'Observable',
          Expression: 'PREVIOUS{[gold]}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'goldNet',
          FriendlyName: 'Net (30s)',
          Type: 'Observable',
          Expression: 'NET_CHANGE{[gold], WITHIN(30s)}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FlashingCell: {
      FlashingCellDefinitions: [
        {
          Name: 'flashingCell-gold',
          Scope: {
            ColumnIds: ['gold'],
          },
          Rule: 'ANY_CHANGE()',
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

```ts
import {AdaptableReadyInfo, AdaptableApi} from '@adaptabletools/adaptable';
import {
  ColDef,
  GridApi,
  IServerSideDatasource,
  IServerSideGetRowsParams,
  IServerSideGetRowsRequest,
} from 'ag-grid-enterprise';

export const onAdaptableReady = ({
  adaptableApi,
  agGridApi,
}: AdaptableReadyInfo) => {
  agGridApi.setGridOption(
    'serverSideDatasource',
    createDataSource(adaptableApi)
  );

  // Tick Gold on visible rows so Observable Calculated Columns can read
  // client-side change history (the only history AdapTable has under SSRM).
  setInterval(() => {
    const nodes = adaptableApi.gridApi
      .getVisibleRowNodes()
      .filter(node => node?.data);
    if (!nodes.length) {
      return;
    }
    const node = nodes[Math.floor(Math.random() * Math.min(8, nodes.length))];
    const pk = adaptableApi.gridApi.getPrimaryKeyValueForRowNode(node);
    if (pk == null) {
      return;
    }
    adaptableApi.gridApi.setCellValue({
      primaryKeyValue: pk,
      columnId: 'gold',
      newValue: (node.data.gold ?? 0) + 1,
    });
  }, 1500);
};

const API_BASE: string = process.env.NEXT_PUBLIC_ATHLETES_API_URL!;

// v24 Column Filters are Expressions. The athletes mock server evaluates
// both Grid Filter and Column Filters from `gridFilterAST`.
function rewriteAutoGroupColumn(
  node: unknown,
  groupedColumnId?: string
): unknown {
  if (!groupedColumnId || node == null || typeof node !== 'object') {
    return node;
  }
  if (Array.isArray(node)) {
    return node.map(item => rewriteAutoGroupColumn(item, groupedColumnId));
  }
  const astNode = node as {type?: string; args?: unknown[]};
  if (astNode.type === 'COL' && astNode.args?.[0] === 'ag-Grid-AutoColumn') {
    return {
      ...astNode,
      args: [groupedColumnId, ...astNode.args.slice(1)],
    };
  }
  if (!astNode.args) {
    return node;
  }
  return {
    ...astNode,
    args: astNode.args.map(arg =>
      rewriteAutoGroupColumn(arg, groupedColumnId)
    ),
  };
}

function appendExpressionAst(
  astNodes: unknown[],
  ast: unknown,
  groupedColumnId?: string
) {
  if (ast == null) {
    return;
  }
  const nodes = Array.isArray(ast) ? ast : [ast];
  for (const node of nodes) {
    astNodes.push(rewriteAutoGroupColumn(node, groupedColumnId));
  }
}

function toServerFilterFields(
  adaptableApi: AdaptableApi,
  groupedColumnId?: string
) {
  const filterState = adaptableApi.stateApi.getAdaptableFilterState();
  const astNodes: unknown[] = [];
  appendExpressionAst(astNodes, filterState.gridFilterAST, groupedColumnId);
  for (const columnFilter of filterState.columnFilters ?? []) {
    if (!columnFilter.Expression) {
      continue;
    }
    appendExpressionAst(
      astNodes,
      adaptableApi.expressionApi.getASTForExpression(columnFilter.Expression),
      groupedColumnId
    );
  }
  return {
    adaptableFilters: [],
    gridFilterAST: astNodes.length ? astNodes : undefined,
  };
}

function createDataSource(adaptableApi: AdaptableApi): IServerSideDatasource {
  return {
    getRows(params: IServerSideGetRowsParams) {
      const getRowsRequest: IServerSideGetRowsRequest = params.request;
      const adaptableSortState = adaptableApi.stateApi.getAdaptableSortState();
      const groupedColumn = getRowsRequest.rowGroupCols?.[0];
      const serverFilters = toServerFilterFields(
        adaptableApi,
        groupedColumn?.field ?? groupedColumn?.id
      );

      const sortModel = getRowsRequest.sortModel.map(sort => {
        const customSort = adaptableSortState.customSorts.find(
          customSort => customSort.ColumnId === sort.colId
        );
        if (customSort) {
          return {
            ...sort,
            sortedValues: customSort.SortedValues,
          };
        }
        return sort;
      });

      const request = {
        ...getRowsRequest,
        sortModel,
        ...serverFilters,
        includeSQL: true,
      };
      console.log('request to server', request);

      fetch(API_BASE, {
        method: 'post',
        body: JSON.stringify(request),
        headers: {'Content-Type': 'application/json; charset=utf-8'},
      })
        .then(httpResponse => httpResponse.json())
        .then(response => {
          params.success({
            rowData: response.rows,
            rowCount: response.lastRow ?? 0,
          });

          if (response.pivotFields?.length) {
            addPivotColumnDefs(response, params.api);
          } else {
            if (!adaptableApi.layoutApi.isCurrentLayoutPivot()) {
              if (params.api.getPivotResultColumns() != null) {
                params.api.setPivotResultColumns(null);
              }
            }
          }

          adaptableApi.systemStatusApi.setInfoSystemStatus(
            `SQL: ${response.sql.slice(0, 40)}`,
            response.sql
          );
        })
        .catch(error => {
          params.fail();
        });
    },
  };
}

function addPivotColumnDefs(response: any, agGridApi: GridApi) {
  const existingPivotColDefs = agGridApi.getPivotResultColumns();
  if (existingPivotColDefs && existingPivotColDefs.length > 0) {
    return;
  }

  const pivotColDefs: ColDef[] = response.pivotFields.map(function (
    field: any,
    index: number
  ) {
    const [_key, value] = Object.entries(field)[0];
    const valueStr = `${value}`;
    return {
      headerName: valueStr,
      field: valueStr,
      filter: false,
      colId: `${Date.now()}_${index}`,
      cellDataType: 'number',
    };
  });

  agGridApi.setPivotResultColumns(pivotColDefs);
}
```

**`src/gridOptions.ts`**

```ts
import {GridOptions, themeBalham} from 'ag-grid-enterprise';

import {columnDefs} from './columnDefs';

export const gridOptions: GridOptions = {
  theme: themeBalham,
  defaultColDef: {
    sortable: true,
    filter: true,
    floatingFilter: true,
    enableRowGroup: true,
  },
  rowModelType: 'serverSide',
  columnDefs: columnDefs,
  sideBar: ['adaptable', 'columns', 'filters'],
};
```

**`src/columnDefs.ts`**

```ts
import {ColDef} from 'ag-grid-enterprise';

export const columnDefs: ColDef[] = [
  {field: 'id', hide: true, cellDataType: 'text'},
  {
    field: 'athlete',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'gold',
    aggFunc: 'sum',
    cellDataType: 'number',
    enableValue: true,
    resizable: true,
  },
  {
    field: 'goldTicks',
    headerName: 'Gold Ticks',
    type: ['calculatedColumn'],
    cellDataType: 'number',
  },
  {
    field: 'prevGold',
    headerName: 'Previous Gold',
    type: ['calculatedColumn'],
    cellDataType: 'number',
  },
  {
    field: 'goldNet',
    headerName: 'Net (30s)',
    type: ['calculatedColumn'],
    cellDataType: 'number',
  },
  {
    field: 'country',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'sport',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
];
```

## Aggregated and Window

The more complex type of Calculated Columns ([Aggregated](https://www.adaptabletools.com/docs/handbook-calculated-column-aggregated/index.md), [Cumulative](https://www.adaptabletools.com/docs/handbook-calculated-column-cumulative/index.md), [Quantile](https://www.adaptabletools.com/docs/handbook-calculated-column-quantile/index.md) and [Window](https://www.adaptabletools.com/docs/handbook-calculated-column-window/index.md)) **do not work** out of the box when using the Server-Side Row Model.

- This is because they use [Aggregated Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-aggregation/index.md) (including [Window Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md))
- These operate over **multiple** rows, not all of which will be present on the client

When this is the case, you will need to follow a 2-stage process:

1. [evaluate the Expression on the Server](https://www.adaptabletools.com/docs/adaptable-server-evaluation/index.md)
2. Pass AdapTable the value returned from the server to render
