windowsforms-11545-controls-and-libraries-pivot-grid-data-shaping-filtering-filter-by-summaries.md
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.
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.
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:
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.
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();
}
}
}
}
Imports System
Imports System.Windows.Forms
Imports DevExpress.XtraEditors
Imports DevExpress.XtraPivotGrid
Namespace XtraPivotGrid_ApplySummaryFilter
Partial Public Class Form1
Inherits XtraForm
Public Sub New()
InitializeComponent()
excelDataSource1.FileName = "SalesPerson.xlsx"
excelDataSource1.Fill()
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
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()
End Try
End Sub
End Class
End Namespace
Filter by summaries has the following limitations:
You cannot filter by summaries in the following cases: