Filter

• Jun 21, 2024
• 5 minutes to read

Filters can be applied in the Filter Editor and Filter Panel at runtime or in code.

Tip

Demo: Filtering

Enable the Filter mode to synchronize filters applied in the field filter and Filter Editor You can customize the resulting filter string or clear it to reset the applied filters. Set the PivotGridWebOptionsFilter.FilterPanelMode to Filter to enable this mode.

Note

The filter expression is not supported in OLAP mode.

Filter Editor

The Filter Editor enables users to build complex filter criteria with an unlimited number of filter conditions, combined by logical operators. The resulting filter conditions are combined with the field-based filter criteria using the AND Boolean operator. The Filter Editor provides a set of logical operators that simplify the process of creating filters for text, numeric, and date-time fields.

Users can invoke the Filter Editor at runtime in a context menu:

To disable access to the Filter Editor from the context menu, set the PivotGridOptionsCustomization.FilterPanelVisible property to Never. In this instance, the Show Filter Editor context menu item is hidden, and you will be able to show the Filter Editor on the client-side using the ASPxClientPivotGrid.ShowFilterEditor method.

To prevent a particular field from being displayed in the Filter Editor, and used in filter criteria built by users, use the PivotGridFieldOptions.ShowInPrefilter property.

Filter Panel

When a filter condition is specified, ASPxPivotGrid displays a Filter Panel.

The Filter Panel displays a filter expression, a check box used to temporarily enable/disable the filtering, and a clear button used to reset the current filter condition.

The PivotGridOptionsCustomization.FilterPanelVisible property enables you to control the Filter Panel’s visibility. It also removes the “Show Filter Editor” item from the context menu and, thereby, prevents users from invoking the Filter Editor at runtime.

Apply Filters in Code

To specify filter criteria in code, use the ASPxPivotGrid.Filter property. The returned object exposes the following properties:

WebFilter.Criteria WebFilter.CriteriaString	Gets or sets a filter expression.
BaseFilter.Enabled	Gets or sets whether the filter criteria, specified by the WebFilter.Criteria or WebFilter.CriteriaString property, are enabled.

When you create a filter expression in code, use the PivotGridField.ID property to refer to a field:

C#

protected void Page_Load(object sender, EventArgs e) {
  if (!IsPostBack && !IsCallback)
    //ASPxPivotGrid1.Filter.CriteriaString = "[" + fieldOrderYear.ID + "] = 2018 AND [" + fieldOrderQuarter.ID + "] between (2, 4)";
    ASPxPivotGrid1.Filter.Criteria = 
      CriteriaOperator.And(
        new BinaryOperator(fieldOrderYear.ID, 2018, BinaryOperatorType.Equal), 
        new BetweenOperator(fieldOrderQuarter.ID, 2, 4)
      );
}

VB.NET

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
  If (Not IsPostBack) AndAlso (Not IsCallback) Then
    'ASPxPivotGrid1.Filter.CriteriaString = "[" + fieldOrderYear.ID + "] = 2018 AND [" + fieldOrderQuarter.ID + "] between (2, 4)";
    ASPxPivotGrid1.Filter.Criteria = CriteriaOperator.And(New BinaryOperator(fieldOrderYear.ID, 2018, BinaryOperatorType.Equal), New BetweenOperator(fieldOrderQuarter.ID, 2, 4))
  End If
End Sub

Note

You cannot filter data against data fields. If a data field is used in filter criteria that has been specified in code, an error message is displayed within the Filter Panel. This message also appears if filter criteria contain a field name that does not exist.

For more information about filter criteria syntax, see Pivot Grid Expression Syntax.

The ASPxPivotGrid.FilterCriteriaChanged event fires when the filter criteria are changed: users change the criteria at runtime or the WebFilter.Criteria property is changed. Use the ASPxPivotGrid.Filter option to get access to the Criteria property.

Client-Side Functionality

You can control the Filter Panel and Filter Editor behavior on the client side. The ASPxClientPivotGrid object exposes the following methods that allow you to do this.

ASPxClientPivotGrid.ShowFilterEditor ASPxClientPivotGrid.HideFilterEditor	Invokes/hides the Filter Editor.
ASPxClientPivotGrid.IsFilterEditorVisible	Indicates whether or not the Filter Editor is visible.
ASPxClientPivotGrid.ChangeFilterEnabled	Enables or disables the current filter applied by the Filter Editor.
ASPxClientPivotGrid.ClearFilter	Clears the filter expression applied using the Filter Editor.

Note

Set the ASPxPivotGrid.ClientInstanceName property to specify a unique client-side identifier for the ASPxPivotGrid.

OLAP Filtering Specifics

An OLAP mode introduces several specifics related to ASPxPivotGrid filtering capabilities. In this mode, you can choose between two ways of constructing MDX queries and specify how to process default members.

Default Members

In an OLAP mode, data is filtered against filter fields in a specific way. If a filter field is not visible, only data related to its default member (a member of the corresponding hierarchy that is set as the default on the server) is processed. Otherwise, data is filtered according to the current filter condition.

For visible filter fields whose filter condition is set to ‘Show All’, you can change this behavior via the PivotGridOptionsOLAP.DefaultMemberFields property. To process only data related to default members of these fields, set the PivotGridOptionsOLAP.DefaultMemberFields property to PivotDefaultMemberFields.AllFilterFields.

WHERE Clause vs Subselect Clause

ASPxPivotGrid allows you to choose whether to use a WHERE or subselect clause in MDX queries when filtering data against filter fields. To do this, use the PivotGridFieldOptions.OLAPFilterUsingWhereClause property.

By default, the pivot grid uses a WHERE clause when filtering against filter fields whose condition contains a single member, either included or excluded. Otherwise, the control uses a subselect clause.

The MS SSAS engine implies limitations, as those described below, that may lead to incorrect query results when using WHERE or subselect clauses. If you have any problems related to filtering against filter fields in an OLAP mode, try customizing the PivotGridFieldOptions.OLAPFilterUsingWhereClause property.

Note

If a MDX query contains calculated members with the CurrentMember function (or methods that require an implied CurrentMember reference, like YTD, PeriodsToDate etc.) used in the expression, a subselect clause may lead to incorrect results. If your cube contains such calculated members, consider setting the PivotGridFieldOptions.OLAPFilterUsingWhereClause property to PivotOLAPFilterUsingWhereClause.Always.

Note

If an attribute in the filter area has natural relationships (sometimes referred to as strong relationships) with an attribute in the column or row area, a WHERE clause may return incorrect data. In this instance, set the PivotGridFieldOptions.OLAPFilterUsingWhereClause property to PivotOLAPFilterUsingWhereClause.Never.