Skip to main content
All docs
V24.1

Tutorial 4 - Customize the Pivot Grid Appearance

  • 4 minutes to read

The tutorial explains how to highlight key metrics, such as the products with the highest year-over-year sales growth, and how to customize the control’s appearance. In the previous tutorial, you added the Difference Variation by Year field that displays the percentage difference in sales between the previous and current years, and sorted products from the highest to lowest sales within categories according to total product sales. In this tutorial, you change the appearance of total and grand total cells and apply format rules to the Difference Variation by Year field.

Change the Pivot Grid Appearance

In this section, you will change the appearance of total and grand total cells. You will apply a lighter background color so that you can later emphasize other cells.

Click the Appearances page of the PivotGrid Designer. In the Appearances pane, select TotalCell and specify the BackColor property.

Specify background color of total cells

Select the “Azure” color.

Change cell background color

Apply the same color to GrandTotalCell.

Apply Conditional Formatting

The Pivot Grid includes a Microsoft Excel-inspired conditional formatting feature that allows you to change the appearance of individual cells based on specific conditions. You can highlight important information, identify trends and exceptions, and compare data using the collection of format rules.

Highlight Highest Yearly Sales Increases

To highlight the highest percent increase in sales among products, you need to create a “Format only top or bottom ranked values” rule type and apply it to the target cells.

Open the PivotGrid Designer and go to the Format Rules page. Click Add Format Rule to create a new rule:

Create a new format rule

Apply the following rule’s settings in the Properties pane:

Measure: pivotGridField5

Name: Products with the Highest Increase in Sales

Rule: Format only top or bottom ranked values

The Rule property’s value is displayed as FormatConditionRuleTopBottom.

Configure rule properties

Settings: Format cells by Row and Column field

Column: pivotGridField4

Row: pivotGridField1

Configure rule's intersection settings

The image below displays the Properties pane with the configured rule.

Top/bottom format rule properties

Specify rule’s appearance and condition settings in the Rule pane:

Rank: 20

RankType: Percent

BackColor: 0, 192, 192

Specify background color

The image below displays the Rule pane with the configured appearance and condition settings.

Configure top/bottom format rule settings

When you run the application, the Pivot Grid highlights the top 20% of values in the Difference Variation by Year field (the cutoff value is 20).

Highlight products with the highest increase in sales

Highlight Sales Decline Numbers

To highlight annual negative sales growth for categories, you need to create a “Format based on value” rule type and apply it to total cells.

Add one more rule in the Format Rules page.

Apply the following rule settings in the Properties pane:

Measure: pivotGridField5

Name: Annual Negative Sales Growth

Rule: Format based on value

The Rule property’s value is displayed as FormatConditionRuleValue.

Configure rule properties

Settings: Format cells by Row and Column field

Column: pivotGridField4

Row: pivotGridField2

The image below displays the Properties pane with the configured rule.

Configure value format rule properties

Specify rule’s appearance and condition settings in the Rule pane:

Condition: Less

Value1: 0

BackColor: 255, 128, 128

Specify background color

The image below displays the Rule pane with the configured appearance and condition settings.

Configure value format rule settings

When you run the application, the Pivot Grid highlights the negative difference in sales between product categories for different years.

Highlight annual negative sales growth

Enable End-User Access to Conditional Formatting Rules

End users can manage the created rules in the UI. Set the PivotGridOptionsMenu.EnableFormatRulesMenu property to true to allow users to invoke the Conditional Formatting Rules Manager. To invoke this manager at runtime, right-click any Pivot Grid cell and select Manage Rules… from the Format Rules context menu.

The image below displays the invoked Conditional Formatting Rules Manager with the created rules.

conditional formatting rule manager

Result

The image below shows the resulting Pivot Grid. You can compare multiple indicators according to displayed data:

  • Product sales for several years.
  • Contribution of each product to total sales.
  • Percentage difference in sales between the previous and current years.
  • Products with the highest increase in sales.
  • Annual negative sales growth.

Resulting Pivot Grid

View Example

The following help topics contain information about functionality used in the tutorial: