Nevron Vision for SharePoint Documentation

Filtering

Pivot Engine > Grouping, Filtering and Sorting > Filtering |

The examples in this topic consider the sample data set described in Grouping, Filtering and Sorting:

Filters rules can be assigned to each pivot data grouping. The pivot itself has a collection of global filter rules.

Filtering is performed in two modes - group filtering and records filtering. The pivot engine automatically chooses between them depending on the presence/absence of data grouping expressions. The global pivot filters always operate in records filtering mode. In both cases all filter rules must pass the record/group in order for it to pass the filters.

Filter Rules

A filter rule is defined by a **Type**, **Expression** and **Value** triplet. The filter **Type** defines the type of filter that needs to be performed. Possible values are:

**Expression**

An expression filter rule takes into account only the rule expression. If the expression evaluates to true - the element (record or group) passes the filter, otherwise it is rejected by the filter. For example:

A global filter rule:*Type:Expression, Expression:=Fields!company="Leka"*will pass only the records for the Leka company, which are 7, 8 and 9.

A global filter rule:*Type:Expression, Expression:=Fields!sales>140*will pass only the records with sales larger than 140, which are 6, 7, 8 and 9.

A data grouping filter rule:*Type:Expression, Expression:=SUM(Fields!sales) > 500*for for a root data grouping with group by expression:*=Fields!company*will pass only the groups for companies which have total sales larger than 500, which in our case are Leka and Maison.**Top N**Top/Bottom N filters help you get the top/bottom N elements (records or groups). The Value expression must evaluate to an integer number, which defines the number of elements to pass from the top/bottom. If the filter expression is blank or evaluates to a constant value these filters return the top/bottom elements in their default order. Otherwise the filter expression is evaluated towards all elements and the elements are sorted in decreasing/increasing order, based on the expression value for each element. In this case the filter returns the top/bottom*Bottom N**distinct*values. For example:

A global filter rule:*Type:TopN, Expression:, Value:3*will pass only the top three records, which are: 1,2 and 3.

A global filter rule:*Type:TopN, Expression:=Fields!sales, Value:3*will pass only the top three distinct records by sales, which are: 6,7 and 8.

A global filter rule:*Type:TopN, Expression:=Fields!company, Value:3*will pass the top three distinct company names - in our case all records.

A data grouping filter rule:*Type:TopN, Expression:=SUM(Fields!sales), Value:1*for a root data grouping with group by expression:*=Fields!company*will pass the first company by total sales, which in our case is Leka (Leka total sales are 213+170+145=528, Bigfoot total sales are 100+120+135=355 and Maison total sales are 98+69+340=507),**Top Percent**Top/Bottom Percent filters help you get the the top/bottom percent count of elements (records or groups). The Value expression must evaluate to a floating point number, which defines the percentage in [0;100] range. Top/Bottom Percent filters are identical to Top/Bottom N filters, the difference being that the number of elements is defined as a percentage of the current scope elements count.*Bottom Percent*

Group Filtering

Group filtering is performed by data grouping filtering rules, when the data grouping has grouping expressions (which is the most common case).

The goal of group filtering is to determine whether a data member should be created for a group, produced by the grouping expressions or not. You must use aggregate functions in group filter expressions. For example:

Let

Abe a root data grouping with a single grouping expression:=Fields!companyand a single filter expression:=FIRST(Fields!company)="Maison". The grouping produces the following groups:A1={1,2,3},A2={4,5,6} andA3={7,8,9}. A data member is however generated only for A2, because only the company column value of record 4 is "Maison". Note that the filter expression uses an aggregate function, so only the company column value of records 1, 4 and 7 were taken into account (each one of them is the first record in its group), however since we grouped by company all records in each group are for the same company.Let

Abe a root data grouping with a single grouping expression:=Fields!company.Valueand a single filter expression:=SUM(Fields!profit.Value) > 120.The grouping produces the following groups:A1={1,2,3},A2={4,5,6} andA3={7,8,9}. A data member is however generated only for A1, since the sum of profit values for records {1,2,3} is 145. The sum of profit values for records {4,5,6} and {7,8,9} is 108 and 100 respectively so these groups failed to pass the filter.

Record Filtering

Record filtering is performed by the data grouping filtering rules, when the data grouping does not have grouping expressions. Record filtering is always performed by the global pivot filters.

The goal of record filtering is to determine whether a record from the input recordset should be included in the one and only data member group or not. It makes no sense to use aggregate functions in record filter expressions, since they are always evaluated towards a single record. If all records fail to pass the filters even a single data member is not created for that data grouping. For example:

Let

Abe a root data grouping without grouping expressions, with a single filter:=Fields!sales.Value<100. Also let A has a propertyPwith expression:=SUM(Fields!sales.Value). This results in a single data member with a propertyPthe value of which is (98+69)=167

See Also