How to restrict conditional formatting applied to subtotals in a React Pivot Table?

Introduction

Conditional formatting allows you to highlight cells in a React Pivot Table based on certain conditions. However, there might be scenarios where you need to exclude subtotal values from these conditional formatting rules. By default, you can prevent conditional formatting from being applied to grand total values by setting the applyGrandTotals property to false. However, there is no direct property for excluding subtotal values. This article guides you through a custom approach to prevent conditional formatting from being applied to subtotal values.

Restrict conditional formatting from being applied to subtotal values

In order to restrict conditional formatting from being applied to subtotal values, you need to use the queryCellInfo event in your code. This event is triggered during the rendering of each row and value cells in the pivot table, providing the opportunity to customize the current cell, such as adding or removing styles, editing the value, etc. By handling this event, you can precisely target the subtotal values and restrict conditional formatting from being applied to them. Below is an example code snippet that demonstrates how to achieve this:

[index.js]

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

let dataSourceSettings = {
    conditionalFormatSettings: [
        {
            value1: 0,
            conditions: 'GreaterThan',
            style: {
                backgroundColor: '#f48fb1',
                color: 'black',
                fontFamily: 'Tahoma',
                fontSize: '12px'
            }
        }
    ],
};
function ConditionalFormattingClass() {
    function queryCellInfo(args) {
        let colIndex = Number(args.cell.getAttribute('data-colindex'));
        // Checking if the current cell is a subtotal cell.
        if (args.data[colIndex].axis == 'value' && args.data[colIndex].actualText != "" && 
            args.data[colIndex].isSum && !args.data[colIndex].isGrandSum) {
            // Iterate through cell classes to identify and remove conditional formatting classes
            for(var i=0; i<args.cell.classList.length; i++) {
                if(args.cell.classList.contains("format" + pivotObj.element.id + i)) {
                    // Removing the conditional formatting class
                    args.cell.classList.remove("format" + pivotObj.element.id + i);
                }
            }
        }
    }
    return (
            <PivotViewComponent id='PivotView' ref={(pivotview) => { pivotObj = pivotview; }} 
                dataSourceSettings={dataSourceSettings} allowConditionalFormatting={true} 
                    gridSettings={{ queryCellInfo: queryCellInfo }}>
                <Inject services={[ConditionalFormatting]}/>
            </PivotViewComponent>);
}
export default ConditionalFormattingClass;

In the above code snippet, we check whether the current cell belongs to the subtotal by using properties of the cell data, such as axis, actualText, isSum, and isGrandSum. This effectively filters out the subtotal cells from the rest. Following this, we iterate through the class list of the cell and check whether any of the class names correspond to the pattern for conditional formatting classes ("format" + pivotObj.element.id + i). Once the appropriate conditional formatting class was identified, we removed the specified class from the cell element. This will effectively restrict conditional formatting from being applied to subtotal values.

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

Screenshots

Before implementing the workaround solution,

After implementing the workaround solution,

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

Additional references:

For more details on the conditional formatting feature, please refer to the official Syncfusion documentation.

Conclusion

By following the steps outlined in this article and using the provided code example, you can easily restrict conditional formatting from being applied to subtotals in a 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 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 questions 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!