How to customize the grand total value of a calculated field in an Angular Pivot Table?

Introduction:

When working with pivot table, sometimes you might want to create a calculated field with a conditional formula. Let’s assume you have fields designated as “A” and “B.” You want to create a calculated field based on the values of these fields using a conditional formula. For instance, the formula would be Sum(A) === 0 ? Sum(B) : Sum(A). However, you may notice that after creating the calculated field, the grand total value is not summarized accurately. Instead, it shows the grand total value for one of the fields used in the formula (i.e., “A”).

This occurs because the grand total values of the calculated field are always determined based on the grand total values of the fields used in the formula, not by aggregating each value in the calculated field. This is the actual behavior of the calculated field in our Syncfusion Pivot Table. In this article, we will discuss how to customize the grand total value of a calculated field in a pivot table by using a workaround technique.

Customize the grand total value of a calculated field

In order to customize the grand total value of a calculated field, you need to use the aggregateCellInfo. This event is triggered during the rendering of each cell in the pivot table and allows you to change the value of each cell.

[app.component.html]

<ejs-pivotview #pivotview id="PivotView" (aggregateCellInfo)="aggregateCellInfo($event)" (dataBound)="dataBound($event)">
</ejs-pivotview>

[app.component.ts]

import { PivotViewAllModule } from '@syncfusion/ej2-angular-pivotview';

export class AppComponent {
    public dataSourceSettings: IDataOptions;
    public columnHeader = {};
 
    aggregateCellInfo(args) {
         // Targeting a specific calculated field with the name 'Valuation'
        if ( args.fieldName === 'Valuation' && args.value && 
        args.rowCellType !== 'subTotal' && args.row.colIndex !== undefined ) {
          // Check if the current row is not a 'grand sum'
          if (args.rowCellType !== 'grand sum') {
            // Check if the column's unique name is not yet in the 'customTotal' object
            if (!this.columnHeader[args.column.valueSort.levelName]) {
              // If not present, initialize the value for this column's unique name
              this.columnHeader[args.column.valueSort.levelName] = args.value;
            } else {
              // If present, add the current value to the existing total
              this.columnHeader[args.column.valueSort.levelName] += args.value;
            }
          } else {
              // If row cell type is equal to 'grand sum', we assign the summarized total value
              args.value = this.columnHeader[args.column.valueSort.levelName];
          }
        }
      }
      
    dataBound() {
       this.columnHeader = {};
    }
    
    ngOnInit(): void {

        this.dataSourceSettings = {
            values: [{ name: 'Sold', caption: 'Units Sold' }, { name: 'Amount', caption: 'Sold Amount' },
            {
                name: 'Valuation',
                caption: 'Valuation',
                type: 'CalculatedField',
            }],
            calculatedFieldSettings: [
                {
                  name: 'Valuation',
                  formula: '"Sum(Sold)" == 0 ? "Sum(Amount)" : "Sum(Sold)"',
                  formatString: 'N2',
                },
            ],
            showColumnGrandTotals: false
        };
    }
}

Here’s a breakdown of how the code works:

1. First, we initialize an empty object named “columnHeader” to store the aggregated values of the calculated field. Then, within the aggregateCellInfo event, we target the specific calculated field (i.e., “Valuation”) using the fieldName property.
2. Next, we check if the row cell type is not a sub-total and that the row is not a grand sum using the rowCellType and row.type properties, respectively. If these conditions are satisfied, we check if the current column-level name is already present in the “columnHeader” object.
3. If it is not present, we add the column level name as a key and the current cell value as the corresponding value in the columnHeader object. If the column level name is already in the “columnHeader” object, we add the current cell value to the existing one.
4. Finally, when a cell belongs to a grand total row, we assign the summarized total value from the “columnHeader” object to the current grand value by using the “value” property and clear the “columnHeader” object within the dataBound event.

The following screenshots illustrate the difference before and after implementing the above workaround solution:

Screenshots

Before implementing the workaround solution,

After implementing the workaround solution,

Conclusion

I hope you enjoyed learning how to customize the grand total value of a calculated field in an Angular Pivot Table.

You can refer to our Angular Pivot Table feature tour page to learn about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our Angular Pivot Table example to understand how to create and manipulate data.

For current customers, you can check out our components on the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!