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

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

Declaration

• C#
• VB.NET

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

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

Property Value

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:

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:

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

See Also

OLAP Filtering Specifics

PivotGridFieldOptions Class

PivotGridFieldOptions Members

DevExpress.XtraPivotGrid Namespace