Blog

All Blog Posts  |  Next Post  |  Previous Post

Next Generation Data Grid for Delphi: Calculations

Tuesday, November 5, 2024

TMS Software Delphi  Components

Intro

If you’re developing in Delphi and looking for a powerful, flexible, and highly customizable data grid solution, then TMS FNC Data Grid is the perfect choice. In this blog, we continue on our journey with the TMS FNC Data Grid. The TMS FNC Data Grid offers developers advanced tools for presenting and interacting with data. A key feature is its support for built-in calculations on rows and columns. This functionality allows developers to perform summary operations such as sums, averages, counts, minimum, and maximum values directly within the grid, without the need for external code or complex calculations.

Built-In Calculation Functions

The Grid provides methods to perform calculations on rows and columns, either across the entire dataset or for specific ranges. These functions return values that can be displayed directly or processed further as needed.

Row Calculation Functions

Each row calculation function operates on a specific row and can target all columns or a specified range. Here’s a quick overview:

  • RowAverage: Returns the average of cell values in a row.
  • RowDistinct: Counts unique values in a row.
  • RowMax: Finds the maximum value.
  • RowMin: Finds the minimum value.
  • RowStandardDeviation: Calculates the standard deviation.
  • RowSum: Computes the sum of values in the row.
  • RowCustomCalculation: Allows for a custom calculation, triggered via the OnCustomRowCalculation event.


Column Calculation Functions

These functions operate on columns, similar to the row functions but targeting a specific column index:

  • ColumnAverage: Computes the average of values.
  • ColumnDistinct: Counts distinct values in the column.
  • ColumnMin: Finds the minimum value.
  • ColumnMax: Finds the maximum value.
  • ColumnStandardDeviation: Calculates the standard deviation.
  • ColumnSum: Sums up all values in the column.
  • ColumnCustomCalculation: Executes custom calculation logic, triggered via the OnCustomColumnCalculation event.


Tracking Calculations

A convenient feature of the grid is its support for tracking calculations displayed in a fixed row or column.

To enable these calculations, use the ColumnCalculations or RowCalculations property, where each column / row can have a unique calculation type:

  • gcmSum: Displays the column sum.
  • gcmAverage: Displays the average of the column.
  • gcmCount: Shows the row count.
  • gcmMin: Displays the minimum value.
  • gcmMax: Displays the maximum value.
  • gcmCustom: Displays a custom calculation result.
  • gcmDistinct: Shows the count of distinct values.
  • gcmStandardDeviation: Displays the standard deviation.

These calculations provide real-time insights and summarize key metrics without needing additional code.


Example: Setting Up Column Calculations

Here’s how to initialize a grid with several rows and set up basic column calculations for SUM, AVERAGE, MIN, and MAX:

Grid.Clear;
Grid.DefaultColumnWidth := 150;
Grid.RowCount := 20;
Grid.FixedBottomRowCount := 1; // Reserve a footer row for calculations
Grid.RandomFill(False, 100); // Fill the grid with random values for testing

// Setting up column calculations in the footer row
Grid.ColumnCalculations[0, 'SUM'] := [CreateNormalColumnCalculation(gcmSum)];
Grid.ColumnCalculations[1, 'AVERAGE'] := [CreateNormalColumnCalculation(gcmAvg)];
Grid.ColumnCalculations[2, 'MIN'] := [CreateNormalColumnCalculation(gcmMin)];
Grid.ColumnCalculations[3, 'MAX'] := [CreateNormalColumnCalculation(gcmMax)];

// Update calculations to reflect initial data in the grid
Grid.UpdateCalculations;

TMS Software Delphi  Components


Programmatically Updating Calculations

When values in the grid are updated through code, you might need to refresh the calculations. The following methods are available for on-demand recalculations:

  • UpdateCalculations: Refreshes all calculations across columns.
  • UpdateColumnCalculation(ColumnIndex): Refreshes the calculation for a specific column.
  • UpdateRowCalculation(RowIndex): Refreshes the calculation for a specific row.
These methods ensure that the grid’s calculated values remain accurate and up-to-date, even if data is modified programmatically.

Group Calculations

Group calculations in TTMSFNCDataGrid make it possible to group your data by one or more columns and perform various calculations on the grouped data. Calculations are applied across the grouped rows and displayed in a dedicated summary row.

Here’s a quick look at the available group calculation options:

  • GroupSum: Calculates the sum of a specific column for each group.
  • GroupAverage: Calculates the average of values within a group.
  • GroupMax: Finds the maximum value in a group.
  • GroupMin: Finds the minimum value in a group.
  • GroupDistinct: Counts distinct values within a group.
  • GroupCustomCalculation: For each group, the OnCustomColumnCalculation is called with a specific level, start and end row.

These calculations provide essential insights, especially when working with grouped data, such as segmented sales figures or customer demographics.

Example

Grid.Clear;
Grid.Options.Column.Stretching.Enabled := True;
Grid.RowCount := 40;
Grid.ColumnCount := 5;
Grid.LoadSampleData;
Grid.Group(3);
Grid.GroupCount(1);
Grid.GroupAverage(4);

TMS Software Delphi  Components


Conclusion

Calculations can be helpful in showing additional information on top of your data. This feature simplifies data analysis directly within the grid, enabling real-time metrics such as sums, averages, and custom calculations without external code. The grid’s adaptability and ease of refreshing calculations programmatically make it ideal for dynamic data applications, especially when quick, insightful data summaries are needed.

In the next blog we'll explore how you can import & export data, so stay tuned for more advanced tips and tutorials on leveraging this next-generation data grid for your Delphi projects! Happy coding!



Pieter Scheldeman


  1. Next Generation Data Grid for Delphi: Getting Started

  2. Next Generation Data Grid for Delphi: Adding, Formatting & Converting Data

  3. Next Generation Data Grid for Delphi: Filtering & Sorting

  4. Next Generation Data Grid for Delphi: Grouping

  5. Next Generation Data Grid for Delphi: Webinar Replay Available!

  6. Next Generation Data Grid for Delphi: Cell Controls

  7. Next Generation Data Grid for Delphi: Master-Detail

  8. Next Generation Data Grid for Delphi: Calculations

  9. Next Generation Data Grid for Delphi: Import & Export

  10. Next Generation Data Grid for Delphi: Template



This blog post has not received any comments yet.



Add a new comment

You will receive a confirmation mail with a link to validate your comment, please use a valid email address.
All fields are required.



All Blog Posts  |  Next Post  |  Previous Post