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 PivotGridOptionsOLAP.DefaultMemberFields property. To process only data related to such fields’ default members, set the PivotGridOptionsOLAP.DefaultMemberFields property to PivotDefaultMemberFields.AllFilterFields.
WHERE Clause vs Subselect Clause
Pivot Grid Control 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 PivotGridFieldOptions.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 PivotGridFieldOptions.OLAPFilterUsingWhereClause property.
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 PivotGridFieldOptions.OLAPFilterUsingWhereClause property to PivotOLAPFilterUsingWhereClause.Always.
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 PivotGridFieldOptions.OLAPFilterUsingWhereClause property to PivotOLAPFilterUsingWhereClause.Never.