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 detail 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 detail 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 detail level or to the specified one.
Gets or sets a column field used to identify a detail level to which the filtering is applied.
Gets or sets the row field used to identify a detail 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 detail 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 detail 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.
using System;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.XtraPivotGrid;
namespace XtraPivotGrid_ApplySummaryFilter {
public partial class Form1 : XtraForm {
public Form1() {
InitializeComponent();
excelDataSource1.FileName = "SalesPerson.xlsx";
excelDataSource1.Fill();
}
private void Form1_Load(object sender, EventArgs e) {
pivotGridControl1.BeginUpdate();
try {
// Set the minimum displayed summary value.
fieldExtendedPrice1.SummaryFilter.StartValue = 500;
// Set the maximum displayed summary value.
fieldExtendedPrice1.SummaryFilter.EndValue = 2500;
// Apply summary filter to the aggregation level specified by the RowField and ColumnField values.
fieldExtendedPrice1.SummaryFilter.Mode = PivotSummaryFilterMode.SpecificLevel;
// Set the row that identifies the filtered aggregation level.
fieldExtendedPrice1.SummaryFilter.RowField = fieldProductName1;
// Set the row that identifies the filtered aggregation level.
fieldExtendedPrice1.SummaryFilter.ColumnField = fieldCountry1;
}
finally {
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).
- Ignores custom values calculated in the CustomCellValue event handler.
You cannot filter by summaries in the following cases:
- The Pivot Grid Control is bound to an OLAP data source.
- You use Running Totals (PivotGridFieldBase.RunningTotal).
- The PivotGridFieldBase.SummaryDisplayType property is not PivotSummaryDisplayType.Default).
You cannot use the CalculationBindingBase descendants to apply summary filter or sorting by summary when Pivot Grid operates with Optimized calculation engine.