How to dynamically apply or remove the conditional formatting for the grand total values in JavaScript Pivot Table?

Introduction

Conditional formatting allows you to highlight cells in a JavaScript Pivot Table based on certain conditions. By default, you can apply conditional formatting to grand total values through the code-behind by setting the applyGrandTotals property to true. Conversely, you can remove conditional formatting by setting the same property to false. However, in certain UI situations, you might want to apply or remove this conditional formatting to grand total values during runtime. This can be achieved by using the context menu option. This article will illustrate how to achieve this using coding examples.

Define conditional format settings

To begin with, the allowConditionalFormatting property must be set to true to enable the conditional formatting feature in the Pivot Table. Subsequently, you need to define conditional formatting for the grand total values by using the conditionalFormatSettings property within the dataSourceSettings. By using this property, you can specify the conditions and style that you want to apply to the value fields.

Here’s an example of how to set up conditional formatting rules:

dataSourceSettings: {
  conditionalFormatSettings: [
    // Conditional formatting for the 'Sold' field
    {
      measure: 'Sold',
      value1: 600,
      conditions: 'GreaterThan',
      style: {
        backgroundColor: '#80cbc4',
      },
    },
    // Formatting for the 'Amount' field
    {
      measure: 'Amount',
      value1: 1000000,
      value2: 14000000,
      conditions: 'Between',
      style: { 
        backgroundColor: 'pink',
      },
    },
  ],
}

In the above code snippet, we define two conditional format settings, one for the “Sold” field and another for the “Amount” field. In the “Sold” field, the GreaterThan condition is specified. If a value is greater than 600, it will be formatted with a background color of “#80cbc4”. Similarly, for the “Amount” field, we specify the Between condition. Any value between 1,000,000 and 14,000,000 will be formatted with a background color of pink.

Define context menu items

To apply conditional formatting to the grand total values at runtime, you can use the built-in context menu options in the pivot table, which can be opened with a simple right-click action. Below is an example of how you can define these context menu items within the gridSettings.

  gridSettings: {
    contextMenuItems: [
      { text: 'Include Grand totals', id: 'include_grandTotal',target: 'td.e-gtot' },
      { text: 'Exclude Grand totals', id: 'exclude_grandTotal',target: 'td.e-gtot' },
    ],
  },

In the code above, we define two context menu items, such as “Include Grand Totals” and “Exclude Grand Totals”. Both items are associated with grand total value cells by setting the target property to the built-in class name td.e-gtot.

Apply or remove conditional formatting at runtime

You need to use the contextMenuClick event and define the logic to either include or exclude the conditional formatting for the grand total values based on the selected context menu item.

  gridSettings: {
    contextMenuClick(args) {
      for (var i = 0; i < pivotObj.dataSourceSettings.conditionalFormatSettings.length; i++) {
        if (pivotObj.dataSourceSettings.conditionalFormatSettings[i].measure == args.rowInfo.rowData[args.rowInfo.cellIndex].actualText) {
          if (args.item.id === 'include_grandTotal') {
            pivotObj.dataSourceSettings.conditionalFormatSettings[i].applyGrandTotals = true;
          } else if (args.item.id === 'exclude_grandTotal') {
            pivotObj.dataSourceSettings.conditionalFormatSettings[i].applyGrandTotals = false;
          }
        }
      }
    },
  },

In the provided code snippet, we iterate through the conditional formatting settings to check if the clicked cell value corresponds to any of the grand total value fields specified in step one. If this condition is true and the id of the selected context menu item matches “include_grandTotal”, we proceed to enable conditional formatting for the respective grand total value field by setting the applyGrandTotals property to true. Conversely, if the id matches “exclude_grandTotal”, we remove the conditional formatting by setting the applyGrandTotals property to false.

The following GIF image portrays the results of the code snippet mentioned above.

GIF

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 dynamically apply or remove the conditional formatting for the grand total values in Pivot Table.

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