How to hide values in PivotTable based on specified conditions?

This article explains how to hide values in Pivot Table based on specified conditions.

Hide values in the pivot table based on specified conditions

When working with a pivot table, you may want to hide value cells based on a certain condition. This can be achieved by utilizing the queryCellInfo event in the gridSettings of the pivot table. This event is triggered for each cell in the pivot table, allowing you to modify the content of the cells based on your requirements.

Below is an example code snippet that demonstrates how to achieve this:

[index.js]

var pivotObj = new ej.pivotview.PivotView({
  gridSettings: {
    queryCellInfo: function (args) {
      var colIndex = Number(args.cell.getAttribute('data-colindex'));
      var rowIndex = Number(args.cell.getAttribute('index'));
      var pivotValue = args.data[colIndex];
      // Here, We have hidden the "Units Sold" pivot table cells that were less than "40000."
      if ( pivotValue.axis === 'value' && pivotValue.actualText === 'Sold' && pivotValue.value < 40000) {
          args.cell.querySelector('.e-cellvalue').innerText = '';
          //Here, we have updated the pivot values to reflect these changes in exports.
          pivotObj.pivotValues[rowIndex][colIndex].formattedText = '';
          pivotObj.pivotValues[rowIndex][colIndex].value = null;
      }
    },
  }
});

The following steps explain the above code snippet:

1. First, we use the data-colindex attribute to get the column index of the cell and the index attribute to get the row index. We retrieve the pivot value using these column and row index.

2. Next, we apply our condition to the retrieved pivot value. In this example, we check if the pivot value belongs to the Sold field and if it is less than 40000.

3. If the condition is met, we clear the text content of the cell using the innerText property of the cell’s e-cellvalue class. This hides the value in the cell.

4. To reflect these changes in exporting, we update the pivot values by assigning an empty string to the formattedText property and setting the value to null.

The following screenshot, which portrays the difference between before and after applying the condition,

Screenshots
Before applying condition

For a practical demonstration, refer to the sample of stackblitz.

Conclusion:
I hope you enjoyed learning how to hide values in Pivot Table based on specified conditions.

You can also refer to our 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 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!