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.

Operator

Description

>

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

Like

Expression 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 Like

Expression 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

Returns the top N ranked records, where N is an Integer.

At first the records are ordered by the Expression in descending order and then the rank is assigned. If two or more records tie for a rank, each tied records receives the same rank and the filter may return more than the exact count specified in Value.

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

Bottom N

Returns the bottom N ranked records, where N is an Integer.

At first the records are ordered by the Expression in descending order and then the rank is assigned. If two or more records tie for a rank, each tied records receives the same rank and the filter may return more than the exact count specified in Value.

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

Top %

Returns the top N percentage of ranked records.

At first the records are ordered by the Expression in descending order and then the rank is assigned. The N is calculated as a percentage of the maximum rank. If two or more records tie for a rank, each tied records receives the same rank and the filter may return more than the exact percent specified in Value.

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.

Bottom %

Returns the bottom N percentage of ranked records.

At first the records are ordered by the Expression in descending order and then the rank is assigned. The N is calculated as a percentage of the maximum rank. If two or more records tie for a rank, each tied records receives the same rank and the filter may return more than the exact percent specified in Value.

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.

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 group members and the filter Expression can contains Aggregate functions.

Report filters limit the report data and Aggregate functions are not allowed.

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.