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

Declaration

[DefaultValue(PivotOLAPFilterUsingWhereClause.SingleValuesOnly)]
[PropertyCategory(PropertyCategory.OLAPSource)]
public PivotOLAPFilterUsingWhereClause OLAPFilterUsingWhereClause { get; set; }
<PropertyCategory(PropertyCategory.OLAPSource)>
<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.

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
PivotGridField
.Options.OLAPFilterUsingWhereClause
ASP.NET Controls and MVC Extensions PivotGridField
.Options.OLAPFilterUsingWhereClause
PivotGridField
.Options.OLAPFilterUsingWhereClause
MVCxPivotGridField
.Options.OLAPFilterUsingWhereClause
MVCxPivotGridField
.Options.OLAPFilterUsingWhereClause
Reporting XRPivotGridField
.Options.OLAPFilterUsingWhereClause
XRPivotGridField
.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