Skip to Content (Press Enter)
UserImage.jpg
Tommy Tan
12Views
2Comments
[DataGrid Reactive Dynamic Columns] How to create Multiple Aggregate Statistics for a Datagrid Numbers Column?
Question
DataGrid
Forge
datagrid-reactive-dynamic-columns
Reactive icon
DataGrid Reactive Dynamic Columns (O11)
Forge asset by Henrique Silva

Hi Everyone

I would like to have multiple aggregate statistics for a datagrid numbers column. The standard one allows for only one aggregate statistic. Use case is to show min, max, mean, median and standard deviation for the column values. This is easy to do in an Excel spreadsheet, but problematic in an Outsystems  DataGrid.

I have implemented a solution using a custom datagrid column, and it broke with the latest update of the OutSystems Datagrid. Creating a custom solution makes it more troublesome for maintenance with each upgrade. 

The basic requirements are that:

1. When you sort on a column, the summary statistics should not be sorted and should stay at the bottom of the column so any suggestion to just add the statistics as extra rows of the column data would fail this sort feature.

2. It should scroll left and right when you scroll the columns of the main table.  So any suggestion to create a separate grid table would not work because the two tables will not scroll together.

3. If you shift a data column in the data grid, the statistics should shift together with the data column. Another reason why a separate table for the statistics does not work.

I understand that another forge component AG Datagrid allows for multiple statistics and is more powerful than the Outsystems Datagrid but that is entirely in javascript and everything has to be rebuilt in javascript which defeats the whole point of the Outsystems user interface.

Thanks for any ideas that would help.

T

0
0
13 days ago
Copy Link
2026-01-28 16-57-48
Mihai Melencu
Champion

Hi @Tommy Tan ,

You can use the following JS in the OnInitialize event of the grid, this will add the footer values only under that column, while still keeping them attached to the Data Grid footer area, so they will not be sorted as normal rows and will stay aligned when scrolling horizontally or moving columns. 

Result:

  1. const gridObj = OutSystems.GridAPI.GridManager.GetGridById($parameters.GridWidgetId);
  2. const grid = gridObj.provider;

  4. const targetBinding = $parameters.ColumnBinding;

  6. const statistics = [
  7.     { label: "Min", key: "min" },
  8.     { label: "Max", key: "max" },
  9.     { label: "Mean", key: "mean" },
  10.     { label: "Median", key: "median" },
  11.     { label: "Std Dev", key: "stdDev" }
  12. ];

  14. function getValueByBinding(item, binding) {
  15.     return binding.split(".").reduce((obj, key) => {
  16.         return obj ? obj[key] : undefined;
  17.     }, item);
  18. }

  20. function parseNumber(value) {
  21.     if (value === null || value === undefined || value === "") {
  22.         return null;
  23.     }

  25.     if (typeof value === "number") {
  26.         return Number.isFinite(value) ? value : null;
  27.     }

  29.     const parsed = Number(
  30.         String(value)
  31.             .replace(/\s/g, "")
  32.             .replace(",", ".")
  33.     );

  35.     return Number.isFinite(parsed) ? parsed : null;
  36. }

  38. function getNumericValues() {
  39.     const items = grid.collectionView ? grid.collectionView.items : [];

  41.     return items
  42.         .map(item => parseNumber(getValueByBinding(item, targetBinding)))
  43.         .filter(value => value !== null);
  44. }

  46. function calculateMedian(values) {
  47.     if (!values.length) {
  48.         return null;
  49.     }

  51.     const sorted = [...values].sort((a, b) => a - b);
  52.     const middle = Math.floor(sorted.length / 2);

  54.     if (sorted.length % 2 === 1) {
  55.         return sorted[middle];
  56.     }

  58.     return (sorted[middle - 1] + sorted[middle]) / 2;
  59. }

  61. function calculateStdDev(values) {
  62.     if (values.length < 2) {
  63.         return null;
  64.     }

  66.     const mean = values.reduce((sum, value) => sum + value, 0) / values.length;

  68.     const variance = values.reduce((sum, value) => {
  69.         return sum + Math.pow(value - mean, 2);
  70.     }, 0) / (values.length - 1);

  72.     return Math.sqrt(variance);
  73. }

  75. function formatNumber(value) {
  76.     if (value === null || value === undefined || Number.isNaN(value)) {
  77.         return "";
  78.     }

  80.     return Number(value).toLocaleString(undefined, {
  81.         minimumFractionDigits: 0,
  82.         maximumFractionDigits: 2
  83.     });
  84. }

  86. function calculateStatistics() {
  87.     const values = getNumericValues();

  89.     const mean = values.length
  90.         ? values.reduce((sum, value) => sum + value, 0) / values.length
  91.         : null;

  93.     return {
  94.         min: values.length ? Math.min(...values) : null,
  95.         max: values.length ? Math.max(...values) : null,
  96.         mean: mean,
  97.         median: calculateMedian(values),
  98.         stdDev: calculateStdDev(values)
  99.     };
  100. }

  102. function ensureFooterRows() {
  103.     grid.beginUpdate();

  105.     while (grid.columnFooters.rows.length < statistics.length) {
  106.         grid.columnFooters.rows.push(new wijmo.grid.Row());
  107.     }

  109.     grid.endUpdate();
  110. }

  112. function renderFooterCell(panel, rowIndex, colIndex, cell) {
  113.     if (panel !== grid.columnFooters) {
  114.         return;
  115.     }

  117.     if (rowIndex >= statistics.length) {
  118.         return;
  119.     }

  121.     const column = grid.columns[colIndex];

  123.     if (!column || column.binding !== targetBinding) {
  124.         cell.textContent = "";
  125.         return;
  126.     }

  128.     const result = calculateStatistics();
  129.     const stat = statistics[rowIndex];

  131.     cell.textContent = stat.label + ": " + formatNumber(result[stat.key]);
  132.     cell.style.textAlign = "right";
  133.     cell.style.fontWeight = "600";
  134. }

  136. ensureFooterRows();

  138. grid.formatItem.addHandler(function (s, e) {
  139.     renderFooterCell(e.panel, e.row, e.col, e.cell);
  140. });

  142. function refreshFooter() {
  143.     ensureFooterRows();
  144.     grid.invalidate();
  145. }

  147. if (grid.collectionView) {
  148.     grid.collectionView.collectionChanged.addHandler(refreshFooter);
  149. }

  151. grid.cellEditEnded.addHandler(refreshFooter);
  152. grid.sortedColumn.addHandler(refreshFooter);
  153. grid.loadedRows.addHandler(refreshFooter);

  155. refreshFooter();
3
0
13 days ago
Copy Link
UserImage.jpg
Tommy Tan

Thanks Mihai. I will compare it to a js which I had implemented which worked until I upgraded to the new version of Datagrid which broke it. So I had to roll the upgrade back as I did not have the time to try to fix it. My fix required creating a custom numerical column block.

0
0
3 days ago
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.