Tutorial 3 - Configure Displayed Data for Analysis

• Sep 12, 2022
• 3 minutes to read

The tutorial shows how to analyze data displayed in the Pivot Grid. In the previous tutorial, you created Pivot Grid fields to display product sales by year. This tutorial explains how to create a field that displays the percentage difference in sales between the previous and current year, and sort products from the highest to lowest sales by category according to total product sales.

Add Percent Variance Columns

This section shows how you can add a column that displays yearly changes in product sales. The following image illustrates the resulting UI:

To display the percentage difference in sales between the previous and current years, you need to create a Pivot Grid field and bind it to the result of the specified difference window calculation.

Click Run Designer to invoke the PivotGrid Designer in the smart tag menu.

In the invoked PivotGrid Designer, click Add Field to create a Pivot Grid field.

Specify the following field’s settings in the Properties pane:

Arrange and Configure the Created Field

Area: DataArea

AreaIndex: 1

Caption: Difference Variation by Year

Bind the Field to the Difference Calculation

DataBinding: Difference

DifferenceType: Percentage

Source: Data Source Column

ColumnName: ExtendedPrice

Format Cell Values to Display them as Percentages

FormatString: p

FormatType: Numeric

Hide Empty Columns

Set the PivotGridFieldOptions.HideEmptyVariationItems property to true to hide empty variation columns.

Form1.cs

using System.Windows.Forms;

namespace WinPivot_GettingStarted {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
            // This line of code is generated by the Data Source Configuration Wizard
            // Fill the SqlDataSource
            sqlDataSource1.Fill();
            pivotGridField5.Options.HideEmptyVariationItems = true;
        }
    }
}

Form1.vb

Imports System.Windows.Forms

Namespace WinPivot_GettingStarted
    Partial Public Class Form1
        Inherits Form

        Public Sub New()
            InitializeComponent()
            ' This line of code is generated by the Data Source Configuration Wizard
            ' Fill the SqlDataSource
            sqlDataSource1.Fill()
            pivotGridField5.Options.HideEmptyVariationItems = True
        End Sub
    End Class
End Namespace

The image below shows the Pivot Grid with the Difference Variation by Year field that displays the percentage difference in sales between the previous and current years.

Sort Values by Grand Total Column

The Pivot Grid allows you to sort field values by the corresponding summary values. To estimate the contribution of product sales within each category according to total product sales, sort the Product Name field values by the Extended Price grand total column values.

Select the Product Name field in the PivotGrid Fields pane of the PivotGrid Designer and specify the following property:

PivotGridFieldSortBySummaryInfo.Field: pivotGridField3

When you run the application, the following icon in the Grand Total column header indicates that the Product Name field values are sorted by this column.

End users can right-click the innermost column or row header to sort field values by summary in the UI. You can disable this functionality for users in one of the following ways:

• Set the PivotGridOptionsCustomization.AllowSortBySummary property to false to disable sorting for all fields.
• Set the field’s PivotGridFieldOptions.AllowSortBySummary property to False to restrict the ability to sort field values for the current field.

Result

The resulting Pivot Grid shows sales breakdown by year and percentage difference in sales between the previous and current years. You can see the dynamics in sales between years by category or expand the category and see the contribution of each product separately.

Related Documentation

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

• Summary Display Types
• Window Calculations Overview
• Sorting by Summary
• Data Formatting

Next Step

In the next tutorial, you highlight key metrics, such as the products with the highest year-over-year sales growth, and customize the control’s appearance.

Tutorial 4 - Customize the Pivot Grid Appearance