Using Summary Filters
- 5 minutes to read
Pivot Grid Control provides a comprehensive UI to enable end-users to configure summary filtering with ease. The pivot grid also allows you to keep full control over summary filter settings in code via an extensive API.
End-User Capabilities
End-users can configure summary filters via a summary filter popup window. To invoke this window, click the filter button displayed within the header of a data field.
Use the PivotGridOptionsCustomization.AllowFilterBySummary property (or the PivotGridFieldOptions.AllowFilterBySummary property for individual fields) to specify whether or not end-users are allowed to invoke a summary filter popup window.
To learn how to use summary filter popup windows, see Filtering by Summary Values in the End-User Capabilities section.
For more information about visual elements displayed within these windows, see Summary Filter Popup Window.
Filtering by Summaries in Code
Summary filter settings are encapsulated in the PivotSummaryFilter object exposed via the PivotGridFieldBase.SummaryFilter property.
To configure a summary filter, you should specify the range of summary values that should be included into the pivot grid. If you wish to apply filtering to Total or Grand Total values, additionally provide column and row fields that identify the target data aggregation level.
Note
Use null (Nothing in Visual Basic) values to identify Grand Totals when specifying the target level. Note that if you specify null values for both fields (which identifies the degenerate (Grand Total, Grand Total) level), filtering will be disabled.
To apply summary filtering, use one of the following approaches.
- Call the PivotSummaryFilter.Apply method and pass summary filter settings as its parameters.
Assign summary filter settings to the following properties.
Gets or sets the minimum summary value to be displayed.
Gets or sets the maximum summary value to be displayed.
Gets or sets whether summary filtering should be applied to the last aggregation level or to the specified one.
Gets or sets a column field used to identify an aggregation level to which the filtering is applied.
Gets or sets the row field used to identify an aggregation level to which the filtering is applied.
To avoid unnecessary control updates while customizing these properties, wrap the code in the PivotGridControl.BeginUpdate and PivotGridControl.EndUpdate method calls. To ensure that the PivotGridControl.EndUpdate method is executed even if an exception occurs, you can also wrap calls to the PivotGridControl.BeginUpdate and PivotGridControl.EndUpdate methods in a try…finally statement.
To specify a valid summary filter range in code, you may need to know the maximum and minimum summary values calculated against the current data field. Use the PivotGridFieldBase.GetSummaryInterval method to obtain these values.
Example
The following example shows how to apply a summary filter to PivotGridControl data that belongs to a particular aggregation level.
In this example, Pivot Grid Control displays product sales by country. The summary filter is applied to country totals calculated for individual products, so that only values that fall into the range from 500 to 2500 are included.
The range of included values is specified using the PivotSummaryFilter.StartValue and PivotSummaryFilter.EndValue properties. To enable filtering only for the selected aggregation level, the PivotSummaryFilter.Mode property is set to PivotSummaryFilterMode.SpecificLevel. To identify this level, the PivotSummaryFilter.RowField and PivotSummaryFilter.ColumnField properties are set to fieldProductName and fieldCountry respectively.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-apply-summary-filter-e4390
using System;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
namespace XtraPivotGrid_ApplySummaryFilter {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e) {
// Binds the pivot grid to data.
this.salesPersonTableAdapter.Fill(this.nwindDataSet.SalesPerson);
// Locks the control to prevent excessive updates when multiple properties are modified.
pivotGridControl1.BeginUpdate();
try {
// Sets the minimum summary value to be displayed.
fieldExtendedPrice.SummaryFilter.StartValue = 500;
// Sets the maximum summary value to be displayed.
fieldExtendedPrice.SummaryFilter.EndValue = 2500;
// Specifies that summary filtering should be applied
// to a particular aggregation level.
fieldExtendedPrice.SummaryFilter.Mode = PivotSummaryFilterMode.SpecificLevel;
// Sets the row used to identify an aggregation level
// to which the filtering is applied.
fieldExtendedPrice.SummaryFilter.RowField = fieldProductName;
// Sets the column used to identify an aggregation level
// to which the filtering is applied.
fieldExtendedPrice.SummaryFilter.ColumnField = fieldCountry;
}
finally {
// Unlocks the control.
pivotGridControl1.EndUpdate();
}
}
}
}