# 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.
Ok | 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.