Filter by Summaries

  • 5 minutes to read

PivotGridControl can use summary values to filter data field's values.

SummaryFilter

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.

SummaryFilter

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:

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.

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:

You cannot filter by summaries in the following cases: