RadPivotGrid’s development is gathering momentum and Q3 brings along a major improvement: our pivot grid control for Asp.Net Ajax already comes equipped with a powerful filtering mechanism.

Let’s look straight into what this new feature offers. Filtering in RadPivotGrid supports three modes: report, label and value.

1. Report filters

Report filters are applied on fields that do not participate in the grouping of the data but are still present in the underlying data source:

So, for example, let’s suppose that your data items grouped by RadPivotGrid feature a Category property with 100 unique values. Furthermore, your manager needs a report where the data should be grouped only by those items that fall within only a limited set of Categories (let’s say Beverages, Seafood, Produce) but Category itself should be excluded from the properties the grid is being grouped by. Well, for that kind of pivot view you should apply a report filter on the Category field.

In practice, report filters allow the exclusion of selected field items from the data prior to its grouping. In order for a pivot grid field to become eligible for report filtering it should be added to RadPivotGrid’s fields collection as a PivotGridReportField:

That will effectively make it part of the grid’s filter zone and make report filtering applicable to it.

We went over report filters in RadPivotGrid but how do you switch filtering on? Filtering in RadPivotGrid is turned on/off by … of course, the AllowFiltering property. When set to true it will make RadPivotGrid display a tiny filter image beside each field’s name:

This is the fields filter button which opens up the filter window:

The latter allows the user to exclude data items based on unique key values or apply a wide range of filter operators that fall within two categories: Label and Value filters.

2. Label filters

Label filters control which groups for a given field will remain in the pivot data view after grouping has been performed. If, for example, you have your data grouped  by a Country field and you need only those Country groups of items that relate to Bulgaria you should apply an Equals label filter to the Country field with the respective string filter value of “Bulgaria”:

Label filters are certainly applicable to both RadPivotGrid’s row and column fields.

3. Value filters

Value filters, for their part, allow filtering operations to be performed on the aggregate results. Again, the filtering is applied after the grouping of the data. Here is another example: Suppose you have grouped your data by Country,  aggregated it by Sum of Profit and need only those Country groups of items which cumulative profit falls within a certain range. Then you should apply a Between value filter on the Country field with the corresponding filter values:

Value filters too are applicable to both RadPivotGrid’s row and column fields.

4. Special filter functions.

Whereas most filter operators are self-explanatory, the Top and Bottom ones might not be that obvious. Let’s go over those briefly. They allow three modes: Items, Percent and Sum.

They also expect an aggregate field and a filter value as part of the overall filter condition:

 When a Top/Bottom value filter is applied to a given field with the Items mode, it will select the top/bottom groups for that field sorted by the chosen aggregate field result and having a count given by the filter value. For example, a Top operator with an Items mode and a filter value of 10 on the Sum of Expenses aggregate field will return the 10 groups (of the field filtered on) that have the greatest Sum of Expenses. The Percent mode will return the top/bottom groups which cumulative aggregate values add to the specified percent of the grand total for that field. The Sum mode, in a similar fashion, gets the top/bottom groups which cumulative aggregate values add to the sum specified by the filter value.

5. Filter dialogues

RadPivotGrid’s filtering UI consists of two dialogues. The one that opens first upon clicking a given field’s filter button allows the construction of set, label and value filters. If, however, a Top value filter is selected an extra filter dialogue is opened to take in additional parameters that are mandatory for the filter expression and have been explained above in Special filter functions. Both dialogues you have already seen in the screenshots above and can look over in more detail at our online example dedicated to RadPivotGrid’s filtering.

On a final note, RadPivotGrid’s filtering looks great but there is still more to come – OLAP binding is on our roadmap for the next quarter release, so, please, stand by.