This topic explains the Microsoft®-Excel®-Style Filtering feature of the xamPivotGrid™/ xamPivotDataSelector™ controls and its user functionality and interactions.
Required background
The following topics are prerequisites to understanding this topic:
This topic demonstrates how to get started with the xamPivotGrid control by providing step-by-step procedure for adding this control to a Silverlight application.
Excel-Style filtering (introduced in the 12.2 release) enables users to add value and label filters in xamPivotGrid similar to those in MS Excel PivotTables. (Of course, this can also be performed by manually (or in code) selecting/de-selecting filter members from the filter member trees for the hierarchies in rows, columns, and filters.)
Excel-Style Filtering can be applied to the data from an XmlaDataSource or any class derived from it as long as there is either a XamPivotGrid or XamPivotDataSelector control present in your application.
Excel-Style functionalities capabilities chart
The following table lists the specific capabilities of the Excel-Style Filtering feature and provides a brief explanation of each of them.
Capability
Description
Search
The Excel-Style Filtering’s Search enables users to search all the members of a hierarchy. When a user enters text in the search text box, all members whose captions contain the entered text are selected and all the remaining ones are unselected. The search is not case-sensitive.
Label Filters
Label filters are similar to label filters in MS Excel PivotTables. They allow the user to filter members of a given level by adding filter conditions for the captions of the members. Users can add multiple conditions and specify whether, in order to be selected, a member caption must satisfy all conditions or at least one of them. Label filters are not case-sensitive.
Value Filters
Value filters are analogous to value filters in MS Excel PivotTables. For each measure, users can apply multiple filtering conditions for the values of the totals on any level of a hierarchy.
Using the Excel-Style Filtering User Interface
Searching
The Excel-style filter dialog is displayed by clicking the filter icon () for any of the added hierarchies or measures. For hierarchies, in the upper part of the dialog, above the member tree, there is a search text box. When users enter text in this field, the whole member tree will be searched and the members whose captions (labels) contain the user-entered string will be selected. Members that have children matching the search condition will be expanded. All remaining members will be collapsed and unselected.
The following picture shows a hierarchy being filtered according to a user search string.
Label Filtering
Label filters are similar to the Search functionality in terms that the filtering conditions are, too, applied to the captions of members in a hierarchy. However, while search is performed against the entire member tree, these label filter conditions are defined for the members in a single level of the hierarchy. For example, in the illustration below, the first label filter will be applied only to members of the Category level in the Product Categories hierarchy.
When the advanced filter dialog for a hierarchy is displayed, there is an Add button at the bottom. This button applies multiple label filters to a hierarchy. When more than one condition is defined, the user has the option to specify whether, in order to be selected, a member caption must satisfy all conditions or at least one of them. (By default, the logical operation between the conditions is AND, meaning that all conditions must be satisfied.)
Unlike searching, which is performed as the user types in the search text box, label filter conditions are applied upon pressing the OK button of the filter dialog.
Note
Note
Filter conditions are applied only to the members that have been selected in the member tree (and their selected children) prior to pressing the OK button.
A label filter consists of one or more conditions that are applied to the labels (displayed captions) of the members of the specified hierarchy. Members that do not satisfy these conditions are not included in the resulting slice. For creating a label filter, there are various clauses that can be applied individually or in combinations (thus forming expressions). For a listing of the available clauses, refer to Label Filter clauses reference chart.
Value Filtering
When the advanced filter dialog for a measure is displayed, users can enter filter conditions for the values of the measure. Each condition applies to the total values of the selected measure on the respective level of the hierarchy added as rows or columns. For example, in the picture below, there are two filters defined for the Reseller Sales Amount measure. The first one is applied to the totals of the Category level in the Product Categories hierarchy. In the same way, the second one is applied to the total values in the Calendar Year level of the Date.Calendar hierarchy.
Note
Note
In order to be able to add value filters for a measure all hierarchies that are added to the rows and columns must contain grand totals.
Note
Note
Only one top/bottom filter can be added for a specific level in a hierarchy.
A label value filter consists of one or more conditions that are applied to the total values of a measure in a specified level of hierarchy. Values that do not satisfy these conditions are not included in the resulting slice. For creating a value filter, there are various clauses that can be applied individually or in combinations. For a listing of the available clauses, refer to Value Filtering clauses reference chart.
Filtering Clauses Reference
Label Filtering clauses reference chart
The following table briefly explains the available Label Filtering expression clauses.
Note
Note
The conditions are not case-sensitive.
Filter Clause
Description
Equals
Returns the members that have the same caption as specified in the filtering string.
Does Not Equal
Returns the members whose captions are not equal to the specified filtering string.
Begins With
Returns the members whose captions begin with the specified filtering string.
Does Not Begin With
Returns the members whose captions do not begin with the specified filtering string.
Ends With
Returns the members whose captions end with the specified filtering string.
Does Not End With
Returns the members whose captions do not end with the specified filtering string.
Contains
Returns the members whose captions contain the specified filtering string.
Does Not Contain
Returns the members whose captions do not contain the specified filtering string.
Greater Than
Lexicographically compares the specified string to the members’ captions and returns the members whose captions are strictly greater than the specified filtering string.
Greater Than Or Equal To
Lexicographically compares the specified string to the members’ captions and returns the members whose captions are greater than or equal to the specified filtering string.
Less Than
Lexicographically compares the specified string to the members’ captions and returns the members whose captions are strictly less than the specified filtering string.
Less Than Or Equal To
Lexicographically compares the specified string to the members’ captions and returns the members whose captions are less than or equal to the specified filtering string.
Value Filtering clauses reference chart
The following table briefly explains the available Value Filtering expression clauses.
Note
Note
In the descriptions, n stands for the actual number entered by the user.
Filter Clause
Description
Top Items
Returns the highest n values at the specified level.
Top Percent
Returns the highest values that make up at least n% of the grand total for the selected level.
Top Sum
Returns the highest values from the selected level that have a sum equal to or greater than n .
Bottom Items
Returns to the lowest n values from the specified level.
Bottom Percent
Returns the lowest values that make up at least n% of the grand total for the selected level.
Bottom Sum
Returns the lowest values at the selected level that have a sum equal to or greater than n .
Equals
Returns the items that have a value for the selected level equal to n .
Greater Than
Returns all items whose value at the selected level is strictly greater than n .
Greater Than Or Equal To
Returns all items whose value at the selected level is equal to or greater than n .
Less Than
Returns all items whose value at the selected level is strictly less than n .
Less Than Or Equal To
Returns all items whose value at the selected level is equal to or less than n .
Related Topics
The following topics provide additional information related to this topic.
) for any of the added hierarchies or measures. For hierarchies, in the upper part of the dialog, above the member tree, there is a search text box. When users enter text in this field, the whole member tree will be searched and the members whose captions (labels) contain the user-entered string will be selected. Members that have children matching the search condition will be expanded. All remaining members will be collapsed and unselected.
