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.v23.2.Core.dll

NuGet Packages: DevExpress.PivotGrid.Core, DevExpress.Win.Navigation

Declaration

C#

[DefaultValue(PivotOLAPFilterUsingWhereClause.SingleValuesOnly)]
public PivotOLAPFilterUsingWhereClause OLAPFilterUsingWhereClause { get; set; }

VB.NET

<DefaultValue(PivotOLAPFilterUsingWhereClause.SingleValuesOnly)>
Public Property OLAPFilterUsingWhereClause As PivotOLAPFilterUsingWhereClause

Property Value

Type	Default	Description
PivotOLAPFilterUsingWhereClause	SingleValuesOnly	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.

Available values:

Name	Description
SingleValuesOnly	The WHERE clause is used when the filter condition contains a single field value, either excluded or included into the pivot grid (in OLAP mode). Otherwise, a subselect clause is used.
Always	The WHERE clause is always used when filtering against filter fields in OLAP mode.
Never	The WHERE clause is never used when filtering against filter fields in OLAP mode. A subselect clause is used instead.
Auto	The pivot grid automatically selects when to use the WHERE clause in an MDX query.

Property Paths

You can access this nested property as listed below:

Object Type	Path to OLAPFilterUsingWhereClause
PivotGridFieldBase	.Options .OLAPFilterUsingWhereClause

Remarks

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:

with  
member [Month].[M].[Month XtraPivotGrid Filter] as 'aggregate({{[Month].[M].[Month].members} - {{[Month].[M].[All].UNKNOWNMEMBER, [Month].[M].[Month].&[3]}}})'  
select  
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]  
where  
( [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])

Note

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]))