Defining the Data

Data Filters

To define selection criteria for a report, you can set up two different kinds of filters, Static and Dynamic. Static Filters are set filters that are automatically applied to the report whenever it is run. Dynamic Filters are filters that are defined by the end user at run time.

(Support for defining Dynamic Filters was added in PxPlus 2022.)

Static Filters

The Define Static Filters window is used to set up static selection criteria to filter the data for the report. Data is filtered based on filter sets. A filter set consists of one or more conditions, all of which must be true for the filter set to be true. For each filter set, select any conditions you wish to apply for each table element to be used in the filter.

In addition, a System Value called Static Filter can be placed on the report layout to display a description of the Static filters.

To invoke the Define Static Filters window, select Filters > Static Filters from the Report Designer Data menu.

This window consists of the following:

Element

List of table elements.

Condition

The drop down presents a list of the conditions that can be applied to data filters:

     None
     Equal to <Value1>
     Not equal to <Value1>
     Less than <Value1>
     Greater than <Value1>
     Less than or equal to <Value1>
     Greater than or equal to <Value1>
     Between <Value1> and <Value2> inclusive
     Between <Value1> and <Value2> exclusive
     Any of <Value1>, <Value2>, <Value3>,……
     None of <Value1>, <Value2>, <Value3>,……
     Contains <Value1>
     Starts with <Value1>

Case Sensitive

Check box to indicate if the filter condition is case sensitive.

Value 1, Value 2, …

Enter any value(s) to be used in comparisons. Comparison values can be selected from a set of options consisting of formulas, parameters or other table elements, or fixed text/numeric values can also be entered. Use double quotes ("") to indicate a null value.

Accept (Reject) data if all conditions in this set are met

A filter set also includes an option to accept or reject a data record based on the outcome of its conditions.

If a filter set is evaluated as not true, then the next set is evaluated (and the next) until a set is evaluated as true, at which point the associated option determines whether the record is accepted or rejected. Up to eight filter sets may be defined.

If no filter sets are found to be true, then the inverse of the last filter set option determines if the record is accepted or rejected.

Example 1:

	Set 1	Accept	CustNum	Between 1000 and 1999 inclusive
	Set 2	Accept	CustNum	Between 4000 and 4999 inclusive

The above filter sets would be used to process records from 1000 to 1999 and 4000 to 4999.

Example 2:

Set 1	Accept	Type$ Amount	Equal to "A" Greater than 1000
Set 2	Accept	Type$ Amount	Equal to "B" Greater than 2000
Set 3	Reject	Type$ Amount	Equal to "C" Less than 5000

The above filter sets will process all "A" records with Amount > 1000, all "B" records with Amount > 2000, reject all "C" records or records with Amount < 5000.

Filter Set

A group of buttons located in the bottom left corner of the panel:

Add/Remove Filter Set	The Add or Remove buttons are used to add a new or remove the current filter set.
Move Up/Move Down	New filter sets will be added at the end. The Move Up and Move Down buttons are used to arrange the order in which the filter sets will be evaluated.
Arrow Buttons	Four arrow buttons that are used to navigate through the filter set definitions.

Saves the Static filters and closes the Define Static Filters window.

Cancel

Cancels any changes and closes the Define Static Filters window.

When a report is generated, if a report has one filter set with an Accept option, the filters are analyzed to determine if minimum/maximum values can be applied to the sort sequence of reports using predefined sorts. These can be used to optimize the data retrieval process. In the case of custom sort sequences, an attempt is made to map the sort to an existing internal key so the same optimization may be used.

Dynamic Filters

The Define Dynamic Filters window is used to set up dynamic selection criteria to filter the report. To set up dynamic filters, first select the Use Dynamic Filters check box and then select which report items are to be made available to the end user to set up filter conditions at run time.

In addition, a System Value called Dynamic Filter can be placed on the report layout to display a description of the Dynamic filters.

To invoke the Define Dynamic Filters window, select Filters > Dynamic Filters from the Report Designer Data menu.

This window consists of the following:

Use Dynamic Filters	Select this check box to invoke the end-user Define Filters window at run time. If parameters have been defined for the report, the run-time Define Filters window is accessed by clicking the Advanced Filters hyperlink button on the generic interactive parameter interface. Note: The run-time Define Filters window will not be presented if the report is running in the background.
Select items to include in the Dynamic Filters	Displays a tree view that lists all the data fields (file fields and calculated fields). Select the check box beside the fields that are to be made available to the end user at run time to set up filter conditions. Note: It is recommended that you do not include fields that have been used in Static filters, as the end user may set up conditions that may be overridden by the Static filters, producing confusing results.
Ok	Saves the selections and closes the Define Dynamic Filters window.
Cancel	Cancels any changes and closes the Define Dynamic Filters window.

Define Filters (Run Time)

If the Use Dynamic Filters check box has been selected for a report, at run time, the end-user Define Filters window will be presented (unless the report is running in the background):

If Parameters have been defined for the report, the Advanced Filters hyperlink button on the generic interactive parameter interface is used to invoke the end-user Define Filters window:

The Define Filters window consists of the following:

Include/Exclude/And

Filters can be set to include or exclude records based on conditions. The drop down presents a list of available functions: Include if…, Exclude if…, and And.

A condition can be simple, or it can be compound using multiple Include if…, Exclude if…, or And functions in combination.

Examples:

An Include if... or Exclude if... function followed immediately by one or more And functions means that all conditions must be true for the Include if… or Exclude if… function to be implemented:

Include if... CST_BALANCE Is greater than 0
And CST_DUE_DATE$ Is less than or equal to DAY

The record would only be included if (CST_BALANCE>0) AND (CST_DUE_DATE$<DAY).

Using multiple Include if... functions in succession will result in records being included if any of the Include if… functions are found to be true:

     Include if... CST_BALANCE Is greater than 0
     And            CST_DUE_DATE Is less than or equal to DAY
     Include if... CST_BALANCE Is greater than CREDITLIMIT

The record would be included if either is true:

((CST_BALANCE>0) AND (CST_DUE_DATE$<DAY)) OR ((CST_BALANCE>CREDITLIMIT))

The same is true excluding records with successive Exclude if... functions.

If there is a combination of Include if... and Exclude if... functions, then the record would only be included if the Include if... function was true and the Exclude if... function was false.

Fields to test

The drop down presents a list of available fields to test, which may be data file fields or calculated fields. The fields in this list were selected in the Define Dynamic Filters window when designing the report.

Condition

The drop down presents a list of conditions that can be applied to Dynamic filters:

     Is Equal To
     Is Not Equal To
     Is Less Than
     Is Greater Than
     Is Less Than Or Equal To
     Is Greater Than Or Equal To
     Is Any Of: ?|?|?|…
     Contains
     Starts With

(The "Is Any of: ?|?|?|…" condition was added in PxPlus 2023.)

Case Sensitive

Check box to indicate if the value is case sensitive.

Value to compare

Values are literal values (without quotation marks), and null values are left blank. By default, text values are not case sensitive unless the Case Sensitive check box is selected.

All conditions support a single value to compare, except for the Is Any of: ?|?|?|… condition which can have several assigned values separated by the | (pipe) character.

Some fields may have associated queries. In this case, the Value to compare may be selected by clicking the lookup button to invoke a Select Items window to select single or multiple values:

This window consists of the following:

Find text	Allows the user to search for occurrences of a specified text value. An option to specify whether the search should be case sensitive is also provided.
Selected Records	Browse buttons that allow the user to locate all items that have been selected.
Select	Toggles the check boxes of the highlighted items On/Off, based on their current settings.
Clear	Clears the currently selected items.
OK	Exits the Select Items window and passes the checked items back to the Value to compare.
Cancel	Exits the Select Items window and returns to the main Define Filters window.

(The Select Items window for selecting records was added in PxPlus 2023.)

Delete

Button located beside the grid that is used to remove the selected filter condition.

Apply Filters

(Available when a Filter condition is defined)

Generates the report with the filter condition(s) applied.

Skip Filters

Generates the report without the filter condition(s) applied.

When Dynamic filters are defined, they are temporarily added to the report definition prior to generating the report, and when the report is completed, they are removed.

Defining the Data

Static Filters

Dynamic Filters

Note:
The run-time Define Filters window will not be presented if the report is running in the background.

Note:
It is recommended that you do not include fields that have been used in Static filters, as the end user may set up conditions that may be overridden by the Static filters, producing confusing results.

Define Filters (Run Time)

Defining the Data

Static Filters

Dynamic Filters

Note: The run-time Define Filters window will not be presented if the report is running in the background.

Note: It is recommended that you do not include fields that have been used in Static filters, as the end user may set up conditions that may be overridden by the Static filters, producing confusing results.

Define Filters (Run Time)

Note:
The run-time Define Filters window will not be presented if the report is running in the background.

Note:
It is recommended that you do not include fields that have been used in Static filters, as the end user may set up conditions that may be overridden by the Static filters, producing confusing results.