Nevron Vision for SharePoint Documentation
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:

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 A be a root data grouping with a single grouping expression:=Fields!company and a single filter expression:=FIRST(Fields!company)="Maison". The grouping produces the following groups: A1={1,2,3}, A2={4,5,6} and A3={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 A be a root data grouping with a single grouping expression:=Fields!company.Value and a single filter expression:=SUM(Fields!profit.Value) > 120. The grouping produces the following groups: A1={1,2,3}, A2={4,5,6} and A3={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 A be a root data grouping without grouping expressions, with a single filter:=Fields!sales.Value<100. Also let A has a property P with expression:=SUM(Fields!sales.Value). This results in a single data member with a property P the value of which is (98+69)=167 

 

See Also

 

 


©2016. Nevron Software LLC.

Send Feedback