OLAP Filtering Specifics
- 2 minutes to read
An OLAP mode introduces several specifics related to PivotGridControl filtering capabilities. In this mode, you can choose between two ways of constructing MDX queries and specify how to process default members.
Default Members
In an OLAP mode, data is filtered against filter fields in a specific way. If a filter field is not visible, only data related to its default member (a member of the corresponding hierarchy that is set as the default on the server) is processed. Otherwise, data is filtered according to the current filter condition.
For visible filter fields whose filter condition is set to ‘Show All’, you can change this behavior via the PivotGridControl.OlapDefaultMemberFields property. To process only data related to such fields’ default members, set the PivotGridControl.OlapDefaultMemberFields property to PivotDefaultMemberFields.AllFilterFields.
WHERE Clause vs Subselect Clause
PivotGridControl allows you to choose whether to use a WHERE or subselect clause in MDX queries when filtering data against filter fields. To do this, use the PivotGridField.OlapFilterUsingWhereClause property.
By default, the pivot grid uses a WHERE clause when filtering against filter fields whose condition contains a single member either included or excluded. Otherwise, the control uses a subselect clause.
The MS SSAS engine implies limitations, as those described below, that may lead to incorrect query results when using WHERE or subselect clauses. If you have any problems related to filtering against filter fields in an OLAP mode, try customizing the PivotGridField.OlapFilterUsingWhereClause property.
Note
If a MDX query contains calculated members with the CurrentMember function (or methods that require an implied CurrentMember reference, like YTD, PeriodsToDate etc.) used in the expression, a subselect clause may lead to incorrect results. If your cube contains such calculated members, consider setting the PivotGridField.OlapFilterUsingWhereClause property to FieldOLAPFilterUsingWhereClause.Always.
Note
If an attribute in the filter area has natural relationships (sometimes referred to as strong relationships) with an attribute in the column or row areas, a WHERE clause may return incorrect data. In this instance, set the PivotGridField.OlapFilterUsingWhereClause property to FieldOLAPFilterUsingWhereClause.Never.