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.v21.1.Core.dll

Declaration

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

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:

Library Object Type Path to OLAPFilterUsingWhereClause
Cross-Platform Class Library PivotGridFieldBase
.Options.OLAPFilterUsingWhereClause
WinForms Controls PivotGridField
.Options.OLAPFilterUsingWhereClause
Reporting XRPivotGridField
.Options.OLAPFilterUsingWhereClause
ASP.NET MVC Extensions MVCxPivotGridField
.Options.OLAPFilterUsingWhereClause
ASP.NET Web Forms Controls PivotGridField
.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