Creating Calculated Columns in FlexGrid for ASP.NET Core

In this age, when the amount of data generated grows by the second, developers are always finding ways to derive more information for business needs. This data is often used for analytical purposes like visualization, reporting where computed data can derive from a base dataset; most of it is done on the fly and in-memory.

Calculated columns are one of the ways to achieve this. These columns or fields can be calculated at runtime and displayed accordingly. For example, in a dataset that contains the total sales of each country along with the discount provided, the total revenue can be calculated at runtime by using the amount and discount columns. Similarly, taxes imposed on that sale can also be calculated using the revenue field.

Calculated Fields in ComponentOne FlexGrid

ComponentOne FlexGrid also supports calculated fields using two different methods:

Unbound fields using FormatItem event

In this way, a calculated field function can be created that will take the name of the calculated column and will return the calculated value according to the current item, and then the result can be displayed on the FlexGrid using formatItem event.

Using CalculatedFields of CollectionView

In the 2021v1 version of ComponentOne MVC Core edition, a new CalculatedFields property has been added to the BaseCollectionViewService which can be easily used to add calculated fields to the CollectionView. A calculated field can be created by providing a name and an expression (function) to calculate its value. Now, this field can be used as any other column. Wherever this field is used, the collection view will provide its calculated value. The CalculatedFields property takes a list of these calculated fields. In addition to providing a better implementation method, the calculated fields can also be used with other controls like FlexChart, OLAP, etc.

View the demo here.

Unbound Calculated Columns in FlexGrid

To create unbound calculated columns, first, we will create an object that will return the calculated value according to the item.

const calcFields = {
        Description: (item) => wijmo.format("This {Product} is of {Color} color", item),
        Revenue: (item) => item.Amount * (1 - item.Discount),
        Tax: (item) => calcFields.Revenue(item) * 0.1
}

Here, three calculated fields are added, Description, Revenue, and Tax. Each field has a function that takes the current item as a parameter and returns the calculated value. Notice that the Tax field uses another calculated field to calculate its value. Now, we will create a handler for the FormatItem event to update the values in the calculated columns.

const calcFormatter = (s, e) => {
    if (s.editRange) {
        return;
    }
    if (s.cells === e.panel) {
        let col = s.columns[e.col];
        let header = col.header;
        if (calcFields[header]) {
            let item = s.rows[e.row].dataItem;
            let calcValue = calcFields[header](item);
            e.cell.textContent = wijmo.Globalize.format(calcValue, col.format);
            s.setCellData(e.row, e.col, calcValue, false, false);
        }
    }
}

In the handler, we will check whether the current header is a calculated field. If it is, then call the corresponding function with the current item as the parameter and set the result to the cell as well as the textContent of the cell. After this, we can create a FlexGrid and assign formatItem to this function. Make sure to create columns and provide the correct header of calculated columns:

<c1-flex-grid auto-generate-columns="false" format-item="calcFormatter" id="unboundCalculated">
        <c1-items-source source-collection="Model"></c1-items-source>
        <c1-flex-grid-column binding="ID" is-read-only="true" width="40" align="center"></c1-flex-grid-column>
        <c1-flex-grid-column binding="Country"></c1-flex-grid-column>
        <c1-flex-grid-column binding="Product"></c1-flex-grid-column>
        <c1-flex-grid-column binding="Color"></c1-flex-grid-column>
        <c1-flex-grid-column header="Description" width="400" is-read-only="true"></c1-flex-grid-column>
        <c1-flex-grid-column binding="Amount" format="c2"></c1-flex-grid-column>
        @*calculated columns*@
        <c1-flex-grid-column binding="Discount" format="p0"></c1-flex-grid-column>
        <c1-flex-grid-column header="Revenue" format="c2" align="right" is-read-only="true"></c1-flex-grid-column>
        <c1-flex-grid-column header="Tax" format="c2" align="right" is-read-only="true"></c1-flex-grid-column>
</c1-flex-grid>

Notice how editing a cell automatically updates the calculated fields.

Calculated Columns using CollectionView

To create calculated fields in the collection view, we can simply provide the list of the calculated fields while initializing the data source using the c1-calculated-field tag:

<c1-items-source id="calculatedFld" source-collection="Model">
    <c1-calculated-field name="Description" expression="'This ' + $.Product + ' is of ' + $.Color + ' color'"></c1-calculated-field>
    <c1-calculated-field name="Revenue" expression="$.Amount * (1 - $.Discount)"></c1-calculated-field>
    <c1-calculated-field name="Tax" expression="$.Revenue * 0.1"></c1-calculated-field>
</c1-items-source>

Here we have bound the source data to the Model and added three calculated fields. The $ symbol contains the reference to the current item. Now, we can simply set this as the data source of the FlexGrid:

<c1-flex-grid auto-generate-columns="false" id="boundCalculated" items-source-id="calculatedFld">
    <c1-flex-grid-column binding="ID" is-read-only="true" width="40" align="center"></c1-flex-grid-column>
    <c1-flex-grid-column binding="Country"></c1-flex-grid-column>
    <c1-flex-grid-column binding="Product"></c1-flex-grid-column>
    <c1-flex-grid-column binding="Color"></c1-flex-grid-column>
    <c1-flex-grid-column binding="Description" width="400" is-read-only="true"></c1-flex-grid-column>
    <c1-flex-grid-column binding="Amount" format="c2"></c1-flex-grid-column>
    <c1-flex-grid-column binding="Discount" format="p0"></c1-flex-grid-column>
    <c1-flex-grid-column binding="Revenue" format="c2" align="right" is-read-only="true"></c1-flex-grid-column>
    <c1-flex-grid-column binding="Tax" format="c2" align="right" is-read-only="true"></c1-flex-grid-column>
</c1-flex-grid>

The calculated fields can be displayed in FlexGrid using the binding property same as any simple field.

While visual differences are not noticeable in the displayed result but this implementation is a lot easier and faster than unbound calculated columns.

We can also use this collection view with other controls. For example, the following creates a FlexPie and displays the revenue generated by each country:

<c1-flex-pie items-source-id="calculatedFld" id="boundChart" binding="Revenue" binding-name="Country">
    <c1-flex-pie-datalabel connecting-line="true" position="Outside" content="{Country} - {value:c0}" />
</c1-flex-pie>

As you can observe in the calculated fields screenshot, the revenue of US was set to 0. Therefore, the revenue is also updated in the FlexPie bound to the same data source. This type of synchronization will not be possible while using unbound calculated columns.

You can download the sample used in this blog here.

ComponentOne MVC Core Edition | Documentation