Conditional Formatting

23 Feb 202221 minutes to read

Allows end user to change the appearance of the pivot table value cells with its background color, font color, font family, and font size based on specific conditions.

The conditional formatting can be applied at runtime through the built-in dialog, invoked from the toolbar. To do so, set allowConditionalFormatting and showToolbar properties in ejs-pivotview tag to true. Also, include the item ConditionalFormatting within the toolbar property in ejs-pivotview tag. End user can now see the “Conditional Formatting” icon in toolbar UI automatically, which on clicking will invoke the formatting dialog to perform necessary operations.

<ejs-pivotview id="PivotView" width="100%" height="300" allowConditionalFormatting="true" showFieldList="true" showToolbar="true" toolbar="@(new List<string>() { "ConditionalFormatting" })">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-drilledmembers>
            <e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
        </e-drilledmembers>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year"></e-field>
            <e-field name="Order_Source" caption="Order Source"></e-field>
        </e-columns>
        <e-values>
            <e-field name="In_Stock" caption="In Stock"></e-field>
            <e-field name="Sold" caption="Units Sold"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-filters>
        <e-conditionalformatsettings>
            <e-format measure="In_Stock" conditions="LessThan" value1="5000">
                <e-fieldlist-style backgroundColor="#80cbc4" color="black" fontFamily="Tahoma" fontSize="12px" />
            </e-format>
            <e-format measure="Sold" conditions="Between" value1="3400" value2="40000">
                <e-fieldlist-style backgroundColor="#f48fb1" color="black" fontFamily="Tahoma" fontSize="12px" />
            </e-format>
        </e-conditionalformatsettings>
    </e-datasourcesettings>
    <e-gridSettings columnWidth="100"></e-gridSettings>

</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    ViewBag.drilledMembers = new string[] { "France" };
    return View();
}

output




output




output




output

Conditional formatting can also be included in the pivot table through code-behind using the e-conditionalformatsettings tag. The required properties to apply a new conditional formatting are,

  • measure: Specifies the value field name for which style will be applied.
  • conditions: Specifies the operator type such as equals, greater than, less than, etc.
  • value1: Specifies the start value.
  • value2: Specifies the end value.
  • style: Specifies the style for the cell.

The available style properties in e-field-style tag, to set in value cells are:

Meanwhile, user can also view conditional formatting dialog in UI by invoking showConditionalFormattingDialog method on an external button click which is shown in the below code sample.

<ejs-button id="conditional-formatting-btn" content="Conditional Formatting" isPrimary="true"></ejs-button>
<ejs-pivotview id="PivotView" width="100%" height="300" allowConditionalFormatting="true" showFieldList="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-drilledmembers>
            <e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
        </e-drilledmembers>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year"></e-field>
            <e-field name="Order_Source" caption="Order Source"></e-field>
        </e-columns>
        <e-values>
            <e-field name="In_Stock" caption="In Stock"></e-field>
            <e-field name="Sold" caption="Units Sold"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-filters>
        <e-conditionalformatsettings>
            <e-format measure="In_Stock" conditions="LessThan" value1="1000">
                <e-fieldlist-style backgroundColor="#80cbc4" color="black" fontFamily="Tahoma" fontSize="12px" />
            </e-format>
            <e-format measure="Sold" conditions="Between" value1="500" value2="40000">
                <e-fieldlist-style backgroundColor="#f48fb1" color="black" fontFamily="Tahoma" fontSize="12px" />
            </e-format>
        </e-conditionalformatsettings>
    </e-datasourcesettings>
    <e-gridSettings columnWidth="100"></e-gridSettings>
</ejs-pivotview>

<script>
    document.getElementById("conditional-formatting-btn").addEventListener('click', function () {
        var pivotObj = document.getElementById("PivotView").ej2_instances[0];
        pivotObj.conditionalFormattingModule.showConditionalFormattingDialog();
    });
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

output

output

Conditional formatting for all fields

Allows end user to apply conditional formatting commonly for all value fields just by ignoring the measure property and setting rest of the properties in e-conditionalformatsettings tag.

<ejs-pivotview id="PivotView" width="100%" height="300" allowConditionalFormatting="true" showFieldList="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-drilledmembers>
            <e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
        </e-drilledmembers>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year"></e-field>
            <e-field name="Order_Source" caption="Order Source"></e-field>
        </e-columns>
        <e-values>
            <e-field name="In_Stock" caption="In Stock"></e-field>
            <e-field name="Sold" caption="Units Sold"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-filters>
        <e-conditionalformatsettings>
            <e-format conditions="GreaterThan" value1="500">
                <e-fieldlist-style backgroundColor="#80cbc4" color="black" fontFamily="Tahoma" fontSize="12px" />
            </e-format>
        </e-conditionalformatsettings>
    </e-datasourcesettings>
    <e-gridSettings columnWidth="100"></e-gridSettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    ViewBag.drilledMembers = new string[] { "France" };
    return View();
}

output

Conditional formatting for specific value field

Allows end user to apply conditional formatting to a specific value field by setting the measure property with specific value field name in e-conditionalformatsettings tag.

<ejs-pivotview id="PivotView" width="100%" height="300" allowConditionalFormatting="true" showFieldList="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-drilledmembers>
            <e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
        </e-drilledmembers>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year"></e-field>
            <e-field name="Order_Source" caption="Order Source"></e-field>
        </e-columns>
        <e-values>
            <e-field name="In_Stock" caption="In Stock"></e-field>
            <e-field name="Sold" caption="Units Sold"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-filters>
        <e-conditionalformatsettings>
            <e-format measure="In_Stock" conditions="LessThan" value1="500">
                <e-fieldlist-style backgroundColor="#80cbc4" color="black" fontFamily="Tahoma" fontSize="12px" />
            </e-format>
        </e-conditionalformatsettings>
    </e-datasourcesettings>
    <e-gridSettings columnWidth="100"></e-gridSettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

output

Conditional formatting for specific row or column

You can apply conditional formatting for specific row or column using label option in the pivot table. It can be configured using the e-conditionalformatsettings tag through code behind, during initial rendering. The required settings are:

  • label: Specifies the header name to apply conditions for row or column.
  • conditions: Specifies the operator type such as equals, greater than, less than, etc.
  • value1: Specifies the start value.
  • value2: Specifies the end value.
  • style: Specifies the style for the cell.
<ejs-pivotview id="PivotView"  width="100%" height="300" allowConditionalFormatting="true" showFieldList="true">
                <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
                    <e-formatsettings>
                        <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
                    </e-formatsettings>
                    <e-rows>
                        <e-field name="Country"></e-field>
                        <e-field name="Products"></e-field>
                    </e-rows>
                    <e-columns>
                        <e-field name="Year"></e-field>
                        <e-field name="Order_Source" caption="Order Source"></e-field>
                    </e-columns>
                    <e-values>
                        <e-field name="In_Stock" caption="In Stock"></e-field>
                        <e-field name="Sold" caption="Units Sold"></e-field>
                    </e-values>
                    <e-filters>
                        <e-field name="Product_Categories" caption="Product Categories"></e-field>
                    </e-filters>
                    <e-conditionalformatsettings>
                        <e-format label="Germany" conditions="Between" value1="500" value2="50000">
                            <e-fieldlist-style backgroundColor="#f48fb1" color="black" fontFamily="Tahoma" fontSize="12px" />
                        </e-format>
                    </e-conditionalformatsettings>
                </e-datasourcesettings>
                <e-gridSettings columnWidth="100"></e-gridSettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Editing and removing existing conditional format

Editing and removing existing conditional format can be done through the UI at runtime. To do so, open the conditional formatting dialog and edit the “Value”, “Condition” and “Format” options based on user requirement and click “OK”. To remove a conditional format, click the “Delete” icon besides the respective condition.

output

Event

ConditionalFormatting

The event conditionalFormatting is triggered initially while clicking the “ADD CONDITION” button inside the conditional formatting dialog in-order to fill user specific condition instead of default condition at runtime. To use this event, allowConditionalFormatting property in PivotView must be set to true. It has following parameters -

  • applyGrandTotals - boolean property, by setting this to true user can enable formatting to grand totals.
  • conditions - condition to be filled in conditional formatting dialog.
  • label - Label value for conditional formatting dialog.
  • measure - measure value for the conditional formatting dialog.
  • style - style property of the conditional formatting dialog.
  • value1 - value 1 for conditional formatting dialog.
  • value2 - value 2 for conditional formatting dialog, this is applicable only for selected conditions like Between and NotBetween.
<ejs-button id="conditional-formatting-btn" content="Conditional Formatting" isPrimary="true"></ejs-button>
<ejs-pivotview id="PivotView" width="100%" height="300" allowConditionalFormatting="true" conditionalFormatting="conditionalFormatting" showFieldList="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-drilledmembers>
            <e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
        </e-drilledmembers>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year"></e-field>
            <e-field name="Order_Source" caption="Order Source"></e-field>
        </e-columns>
        <e-values>
            <e-field name="In_Stock" caption="In Stock"></e-field>
            <e-field name="Sold" caption="Units Sold"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-filters>
        <e-conditionalformatsettings>
            <e-format measure="In_Stock" conditions="LessThan" value1="1000">
                <e-fieldlist-style backgroundColor="#80cbc4" color="black" fontFamily="Tahoma" fontSize="12px" />
            </e-format>
            <e-format measure="Sold" conditions="Between" value1="500" value2="40000">
                <e-fieldlist-style backgroundColor="#f48fb1" color="black" fontFamily="Tahoma" fontSize="12px" />
            </e-format>
        </e-conditionalformatsettings>
    </e-datasourcesettings>
    <e-gridSettings columnWidth="100"></e-gridSettings>
</ejs-pivotview>

<script>
    document.getElementById("conditional-formatting-btn").addEventListener('click', function () {
        var pivotObj = document.getElementById("PivotView").ej2_instances[0];
        pivotObj.conditionalFormattingModule.showConditionalFormattingDialog();
    });
    function conditionalFormatting(args){
        args.style.backgroundColor = "Blue";
        args.value1 = 23459;
    }
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}