Filter by Summaries
- 5 minutes to read
PivotGridControl can use summary values to filter data field‘s values.
Summary filters cannot be applied to Totals, Grand Totals, and data cells related to the same data field simultaneously, but can be applied to cells that belong to the same data aggregation level. Initially, summary filters are applied to cells of the last level (the last column and row fields identify the level). To filter Totals and Grand Totals, click “apply to the specific level”.
Summary filters are applied after the Prefilter and field or group filters.
Note
OLAP and server-mode data sources do not support summary filters.
End-user Capabilities
Users can configure summary filters in a pop-up summary filter window. To invoke this window, click the filter button in a data field‘s header.
Use the PivotGridOptionsCustomization.AllowFilterBySummary property (or the PivotGridFieldOptions.AllowFilterBySummary property for individual fields) to enable/disable summary filter popup window for end users.
For more information about how to use the pop-up summary filter window, see Filter by Summary Values in the End-user Capabilities section.
For more information about visual elements in the filter window, see Pop-up Summary Filter Window.
Filter by Summaries in Code
Use the PivotGridFieldBase.SummaryFilter property to access summary filter settings in the PivotSummaryFilter object.
Specify the range of summary values to configure a summary filter. Provide column and row fields that identify the target data aggregation level to apply a filter to Total or Grand Total values.
Note
Use null (Nothing in Visual Basic) values to identify Grand Totals when you specify the target level. If you specify null values for both fields (which identifies the degenerate (Grand Total, Grand Total) level), filtering is disabled.
Use one of the following approaches to apply a summary filter:
- 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.
Wrap the code in the PivotGridControl.BeginUpdate and PivotGridControl.EndUpdate method calls to avoid unnecessary control updates while customizing these properties. To ensure that EndUpdate is always called even if an exception occurs, use the try…finally statement.
Use the PivotGridFieldBase.GetSummaryInterval method to obtain the maximum and minimum summary values of data field and specify the summary filter range in code.
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();
}
}
}
}
Limitations
Filter by summaries has the following limitations:
- Available for numeric data fields only.
- Does not affect custom totals.
- Resets when you change (PivotGridFieldBase.SummaryType).
You cannot filter by summaries in the following cases:
- The Pivot Grid Control is bound to an OLAP datasource;
- You use Running Totals (PivotGridFieldBase.RunningTotal);
- The PivotGridFieldBase.SummaryDisplayType property is not PivotSummaryDisplayType.Default).