Search results

Conditional Formatting

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 EjsPivotView class to true. Also, include the item ToolbarItems.ConditionalFormatting within the Toolbar property in EjsPivotView class. 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.

    @using Syncfusion.EJ2.Blazor.PivotView

    <EjsPivotView TValue="ProductDetails" ShowToolbar="true" Toolbar="@toolbar" AllowConditionalFormatting="true">
            <PivotViewDataSourceSettings DataSource="@data">
                <PivotViewColumns>
                    <PivotViewColumn Name="Year"></PivotViewColumn>
                    <PivotViewColumn Name="Quarter"></PivotViewColumn>
                </PivotViewColumns>
                <PivotViewRows>
                    <PivotViewRow Name="Country"></PivotViewRow>
                    <PivotViewRow Name="Products"></PivotViewRow>
                </PivotViewRows>
                <PivotViewValues>
                    <PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
                    <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
                </PivotViewValues>
                <PivotViewFormatSettings>
                    <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
                </PivotViewFormatSettings>
            </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ToolbarItems> toolbar = new List<ToolbarItems> {
          ToolbarItems.ConditionalFormatting
        };
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
            //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
    }

output

output

output

output

Conditional formatting can also be included in the pivot table through code-behind using the PivotViewConditionalFormatSetting class. 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.
  • PivotViewStyle: Specifies the style for the cell.

The available style properties in PivotViewStyle class, 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.

    @using Syncfusion.EJ2.Blazor.PivotView
    @using Syncfusion.EJ2.Blazor.Buttons

    <EjsButton OnClick="@OnCondFormatting" IsPrimary="true">Conditional Formatting</EjsButton>

    <EjsPivotView TValue="ProductDetails" AllowConditionalFormatting="true">
        <PivotViewDataSourceSettings DataSource="@data">
            <PivotViewColumns>
                <PivotViewColumn Name="Year"></PivotViewColumn>
                <PivotViewColumn Name="Quarter"></PivotViewColumn>
            </PivotViewColumns>
            <PivotViewRows>
                <PivotViewRow Name="Country"></PivotViewRow>
                <PivotViewRow Name="Products"></PivotViewRow>
            </PivotViewRows>
            <PivotViewValues>
                <PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
                <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
            </PivotViewValues>
            <PivotViewFilters>
                <PivotViewFilter Name="Product_Categories" Caption="Product Categories"></PivotViewFilter>
            </PivotViewFilters>
            <PivotViewFormatSettings>
                <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
            </PivotViewFormatSettings>
            <PivotViewConditionalFormatSettings>
                <PivotViewConditionalFormatSetting Measure="Amount" Conditions=Condition.GreaterThan Value1=1000>
                    <PivotViewStyle BackgroundColor="#80cbc4" Color="black" FontFamily="Tahoma" FontSize="12px">
                    </PivotViewStyle>
                </PivotViewConditionalFormatSetting>
                <PivotViewConditionalFormatSetting Measure="Sold" Conditions=Condition.Between  Value1=500 Value2=40000>
                    <PivotViewStyle BackgroundColor="#f48fb1" Color="black" FontFamily="Tahoma" FontSize="12px">
                    </PivotViewStyle>
                </PivotViewConditionalFormatSetting>
            </PivotViewConditionalFormatSettings>
        </PivotViewDataSourceSettings>
    </EjsPivotView>

    @code{
        public List<ProductDetails> data { get; set; }
        protected override void OnInitialized()
        {
            this.data = ProductDetails.GetProductData().ToList();
           //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
        }
        public void OnCondFormatting(Microsoft.AspNetCore.Components.Web.MouseEventArgs args)
        {
            this.pivot.ShowConditionalFormattingDialog();
        }
    }

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 PivotViewConditionalFormatSetting class.

   @using Syncfusion.EJ2.Blazor.PivotView

       <EjsPivotView TValue="ProductDetails" AllowConditionalFormatting="true">
           <PivotViewDataSourceSettings DataSource="@data">
               <PivotViewColumns>
                   <PivotViewColumn Name="Year"></PivotViewColumn>
                   <PivotViewColumn Name="Quarter"></PivotViewColumn>
               </PivotViewColumns>
               <PivotViewRows>
                   <PivotViewRow Name="Country"></PivotViewRow>
                   <PivotViewRow Name="Products"></PivotViewRow>
               </PivotViewRows>
               <PivotViewValues>
                   <PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
                   <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
               </PivotViewValues>
               <PivotViewFilters>
                   <PivotViewFilter Name="Product_Categories" Caption="Product Categories"></PivotViewFilter>
               </PivotViewFilters>
               <PivotViewFormatSettings>
                   <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
               </PivotViewFormatSettings>
               <PivotViewConditionalFormatSettings>
                   <PivotViewConditionalFormatSetting Conditions=Condition.GreaterThan Value1=500>
                       <PivotViewStyle BackgroundColor="#80cbc4" Color="black" FontFamily="Tahoma" FontSize="12px">
                       </PivotViewStyle>
                   </PivotViewConditionalFormatSetting>
               </PivotViewConditionalFormatSettings>
           </PivotViewDataSourceSettings>
       </EjsPivotView>

   @code{
       public List<ProductDetails> data { get; set; }
       protected override void OnInitialized()
       {
           this.data = ProductDetails.GetProductData().ToList();
          //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
       }
   }

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 PivotViewConditionalFormatSetting class.

  @using Syncfusion.EJ2.Blazor.PivotView

      <EjsPivotView TValue="ProductDetails" AllowConditionalFormatting="true">
          <PivotViewDataSourceSettings DataSource="@data">
              <PivotViewColumns>
                  <PivotViewColumn Name="Year"></PivotViewColumn>
                  <PivotViewColumn Name="Quarter"></PivotViewColumn>
              </PivotViewColumns>
              <PivotViewRows>
                  <PivotViewRow Name="Country"></PivotViewRow>
                  <PivotViewRow Name="Products"></PivotViewRow>
              </PivotViewRows>
              <PivotViewValues>
                  <PivotViewValue Name="Sold" Caption="Units Sold"></PivotViewValue>
                  <PivotViewValue Name="Amount" Caption="Sold Amount"></PivotViewValue>
              </PivotViewValues>
              <PivotViewFilters>
                  <PivotViewFilter Name="Product_Categories" Caption="Product Categories"></PivotViewFilter>
              </PivotViewFilters>
              <PivotViewFormatSettings>
                  <PivotViewFormatSetting Name="Amount" Format="C"></PivotViewFormatSetting>
              </PivotViewFormatSettings>
              <PivotViewConditionalFormatSettings>
                  <PivotViewConditionalFormatSetting Measure="Sold" Conditions=Condition.GreaterThan Value1=500>
                      <PivotViewStyle BackgroundColor="#80cbc4" Color="black" FontFamily="Tahoma" FontSize="12px">
                      </PivotViewStyle>
                  </PivotViewConditionalFormatSetting>
              </PivotViewConditionalFormatSettings>
          </PivotViewDataSourceSettings>
      </EjsPivotView>

  @code{
      public List<ProductDetails> data { get; set; }
      protected override void OnInitialized()
      {
          this.data = ProductDetails.GetProductData().ToList();
         //Bind the data source collection here. Refer "Assigning sample data to the pivot table" section in getting started for more details.
      }
  }

output

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