How to customize null values in Pivot Table?

This article explains how to customize null values in React Pivot Table.

Customizing null values in Pivot Table

In the pivot table, null values are displayed based on the bounded data from the data source, which can impact the readability and presentation of your table. To customize the display of null values in a pivot table, you can use the enginePopulated event. This event is triggered after the pivot engine is populated and allows you to customize the pivotValues. By handling this event, you can iterate through the pivot values and replace the null values with a custom representation, such as a hyphen (‘-’) or any other placeholder. Also, the customized values will be reflected in other pivot table features, such as editing, exporting, etc.,

Here is a code example that shows how to customize null values in a pivot table:

[index.js]

import { PivotViewComponent, PDFExport, ExcelExport, Inject, Toolbar } from '@syncfusion/ej2-react-pivotview';

let pivotObj;
let toolbarOptions = ['Export']

function dataBound(args) {
    pivotObj.allowEngineExport= true;
}

function enginePopulated(args) {
    for (var i = 0; i < args.pivotValues.length; i++) {
        for (var j = 0; args.pivotValues[i] != null &amp;&amp; j &lt; args.pivotValues[i].length; j++) {
            if (args.pivotValues[i][j] != null &amp;&amp; args.pivotValues[i][j].axis == 'value') {
                if (args.pivotValues[i][j].formattedText === 'null') {
                    // To Customize the null values
                    args.pivotValues[i][j].formattedText = '-';
                    // To update the customized value for excel exporting
                    args.pivotValues[i][j].value = '-';
                }
            }
        }
    }
}

function Default() {
    return ( 
            <pivotviewcomponent id="PivotView" ref="{(pivotview)" ==""> { pivotObj = pivotview; }} enginePopulated={enginePopulated.bind(this)} showToolbar={true} allowExcelExport={true} allowPdfExport={true} toolbar={toolbarOptions} dataBound={dataBound.bind(this)}&gt;
                <inject services="{[Toolbar," pdfexport,="" excelexport]}="">
             </inject></pivotviewcomponent>
    );
}
export default Default;

In the above code example, we have used the enginePopulated event to iterate through the pivotValues and check the cells with a value axis. If the formattedText of a cell is null, we set the formattedText and value properties of the cell to a hyphen (‘-’).

The following screenshots show the difference between the with and without null value customization.

Screenshots:
Before customizing the null values

After customizing the null values

For a practical example of this code in action, you can refer to the following Sample in Stackblitz

Conclusion:

I hope you enjoyed learning how to customize null values in React Pivot Table

You can refer to our React 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 React Pivot Table example example to understand how to create and manipulate data.

For current customers, you can check out our components from 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!