Reporting

Filters are used to limit the number of records in a report based on specified filter rules. If the conditions of the rules are met the record is included in the report. Multiple rules can be defined that make up a filter. Filters are defined using the the Edit Filter Dialog.

Filter Rules

Each filter rule is made up of an Expression, Operator and Value.

  • Expression can be a data field from the Fields collection or defined using the Edit Expression Dialog.
  • Operator can be simple comparison operators but can also include Like and additional operators to include the top and bottom number of records and the top and bottom percentage of records. See the table of operators below.
  • Value can be a literal value, "<blank>" or an expression defined in the Edit Expression Dialog.

OperatorDescription
>Expression is greater than Value
<Expression is less than Value
>=Expression is greater than or equal to Value
<=Expression is less than or equal to Value
=Expression is equal to Value
<>Expression is not equal to Value
LikeExpression is like Value. Use the "%" wildcard to represent any number of other characters. Example: Fields.ProductName Like "%Bike%" will allow all records where the "ProductName" field contains "Bike".
Not LikeExpression is Not like Value. Use the "%" wildcard to represent any number of other characters. Example: Fields.ProductName NotLike "%Bike%" will allow all records where the "ProductName" field does not contain "Bike".
Top N

Return the top N number of records, where N is an Integer.

If Expression is blank the filter returns the exact number of records specified by Value. For example "Top N 5" returns exactly five records. 

If Expression contains a field or expression the filter returns the top number for that expression. This filter may return more than the exact count specified in Value. For example "ProductCategory Top N 2" might return twenty records containing ProductCategory "Accesories" and "Apparel".

Bottom N

Return the bottom N number of records, where N is an Integer.

If Expression is blank the filter returns the exact number of records specified by Value. For example "Bottom N 5" returns exactly five records. 

If Expression contains a field or expression the filter returns the bottom records for that expression. This filter may return more than the exact count specified in Value. For example "CountryRegion Bottom N 2" might return twenty records containing "Zambia" and "Zimbabwe".

Top %

Returns the top N percentage of records, where N is an Integer.

If Expression is blank the filter returns the percentage of total records specified in Value. If the report has an initial 1000 records and the filter is "Top % 5", 50 records are returned.

If Expression contains a field or expression the filter returns the bottom percentage records that satisfy the expression. For example if the top two percent country codes in a StateProvince table are "AS", "AU" and "CA" might return twenty records containing only those country codes. 

Bottom %

Returns the bottom N percentage of records, where N is an Integer.

If Expression is blank the filter returns the percentage of total records specified in Value. If the report has an initial 1000 records and the filter is "Bottom % 5", 50 records are returned.

If Expression contains a field or expression the filter returns the bottom percentage records that satisfy the expression. For example if the bottom two percent country codes in a StateProvince table are "VI", "US" and "PW" might return twenty records containing only those country codes. 

Filters used at the Group and Report Level

Filtering can be performed at the group and report level through the Filters property. Group filters are intended to limit the number of groups while Report filters limit the report data. For example, consider the simplified data in the example diagram below. The data is first grouped by Country and filtered by Continent where Continenet = 'EU'. This returns two records for Italy and Bulgaria. The data can be further constrained by adding a report filter "City LIKE R%" that returns only "Roma" and "Russe". If the filtering criteria is satisfied, then the group is included in the report. The data within the group is then further limited by the report filter.