PivotGridFieldOptions.OLAPFilterUsingWhereClause Property

Gets or sets whether to use the WHERE or subselect clause in an MDX query, when filtering against filter fields in OLAP mode.

Namespace: DevExpress.XtraPivotGrid

Assembly: DevExpress.PivotGrid.v20.1.Core.dll


public PivotOLAPFilterUsingWhereClause OLAPFilterUsingWhereClause { get; set; }
Public Property OLAPFilterUsingWhereClause As PivotOLAPFilterUsingWhereClause

Property Value

Type Default Description


A PivotOLAPFilterUsingWhereClause enumeration member that specifies whether to use the WHERE or subselect clause in an MDX query, when filtering against filter fields in OLAP mode.

Property Paths

You can access this nested property as listed below:

Library Object Type Path to OLAPFilterUsingWhereClause
Cross-Platform Class Library PivotGridFieldBase
WinForms Controls PivotGridField
ASP.NET Controls and MVC Extensions PivotGridField
Reporting XRPivotGridField


If the field filter condition is set to show a single field value, set the OLAPFilterUsingWhereClause property to SingleValuesOnly.

MDX queries for calculated measures with the CurrentMember function cannot contain subselect clauses. Set the OLAPFilterUsingWhereClause property to Always to execute such queries.

The following mdx query with the Cumulative calculated field results in an error if the Cumulative expression contains the CurrentMember function:

member [Month].[M].[Month XtraPivotGrid Filter] as 'aggregate({{[Month].[M].[Month].members} - {{[Month].[M].[All].UNKNOWNMEMBER, [Month].[M].[Month].&[3]}}})'  
non empty { [Measures].[Number], [Measures].[Cumulative]}  on columns,  
non empty { [Item].[Item].[All], { {[Item].[Item].[Item].members}} }  
 dimension properties MEMBER_UNIQUE_NAME  on rows  
from [demo]  
( [Month].[M].[Month XtraPivotGrid Filter] ) CELL PROPERTIES  VALUE, FORMAT_STRING , LANGUAGE  

The Cumulative expression that produces an error:

Sum({[Month].[M].CurrentMember.Level.Members}.Item(0):[Month].[M].CurrentMember, [Measures].[Number])

The use of the CurrentMember function may result to an error with the text "The MDX function CURRENTMEMBER failed because the coordinate for the attribute contains a set". The cells are empty (OleDb provider) or contains the Error text (other providers).

The following Cumulative expression solves the problem:

iif(Count(Existing [Month].[M].[All].children ) = 1,  
Sum({[Month].[M].CurrentMember.Level.Members}.Item(0):[Month].[M].CurrentMember, [Measures].[Number]) ,  
Sum( [Month].[M].[All].children , [Measures].[Number]))
See Also