You can assign screening conditions to a data source, independent of a request, and activate these screening conditions for use in report requests against the data source. A filter is a packet of definitions that resides at the file level, containing WHERE and/or IF criteria. Whenever a report request is issued against a data source, all filters that have been activated for that data source are in effect. WHERE or IF syntax that is valid in a report request is also valid in a filter. | In this section: Applying Filters to Joined Structures How to: | Reference: Usage Notes for Virtual Fields Used in Filters Example: |
A filter can be declared at any time before the report request is run. The filters are available to subsequent requests during the session in which the filters have been run. For details, see How to Declare a Filter.
Filters allow you to:
In an interactive environment, filters also reduce repetitive ad hoc typing.
Note: Simply declaring a filter for a data source does not make it active. A filter must be activated with a SET command. For details, see How to Activate or Deactivate Filters.
A filter can be described by the following declaration
FILTER FILE filename [CLEAR|ADD]
[filter‑defines;]
NAME=filtername1 [,DESC=text]
where-if phrases
.
.
.
NAME=filternamen [,DESC=text]
where-if phrases
END
where:
filename
Is the name of the Master File to which the filters apply.
CLEAR
Deletes any existing filter phrases, including any previously defined virtual fields.
ADD
Enables you to add new filter phrases to an existing filter declaration without clearing previously defined filters.
filter‑defines
Are virtual fields declared for use in filters. For more information, see Usage Notes for Virtual Fields Used in Filters.
filtername1...filternamen
Is the name by which the filter is referenced in subsequent SET FILTER commands. This name may be up to eight characters long and must be unique for a particular file name.
text
Describes the filter for documentation purposes. Text must fit on one line.
where-if phrases
Are screening conditions that can include all valid syntax. They may refer to data source fields and virtual fields in the Master File; they may not refer to virtual fields declared using a DEFINE command, or to other filter names.
Virtual fields used in filters:
The first example creates the filter named UK, which consists of one WHERE condition. It also adds a definition for the virtual field MARK_UP to the set of virtual fields already being used in filters for the CAR data source.
When a report request is issued for CAR, with UK activated, the condition WHERE MARK_UP is greater than 1000 is automatically added to the request.
Note: The virtual field MARK_UP cannot be explicitly displayed or referenced in the TABLE request.
FILTER FILE CAR ADD
MARK_UP/D7=RCOST‑DCOST;
NAME=UK
WHERE MARK_UP GT 1000
END
The second example declares three named filters for the CAR data source: ASIA, UK, and LUXURY. The filter ASIA contains a textual description, for documentation purposes only. CLEAR, on the first line, erases any previously existing filters for CAR, as well any previously defined virtual fields used in filters for CAR, before it processes the new definitions.
FILTER FILE CAR CLEAR
NAME=ASIA,DESC=Asian cars only
IF COUNTRY EQ JAPAN
NAME=UK
IF COUNTRY EQ ENGLAND
NAME=LUXURY
IF RETAIL_COST GT 50000
END
Filters can be activated and deactivated with the command
SET FILTER= {*|xx[yy zz]} IN file {ON|OFF}
where:
*
Denotes all declared filters. This is the default value.
xx, yy, zz
Are the names of filters as declared in the NAME = syntax of the FILTER FILE command.
file
Is the name of the data source to which you are assigning screening conditions.
ON
Activates all (*) or specifically named filters for the data source. The maximum number of filters you can activate for a data source is limited by the number of WHERE/IF phrases that the filters contain, not to exceed the limit of WHERE/IF criteria in any single report request.
OFF
Deactivates all (*) or specifically named filters for the data source. This value is the default.
Note: The SET FILTER command is limited to one line. To activate more filters than fit on one line, issue additional SET FILTER commands. As long as you specify ON, the effect is cumulative.
The following commands activate A, B, C, D, E, F and deactivate G (assuming that it was set ON previously):
SET FILTER = A B C IN CAR ON
SET FILTER = D E F IN CAR ON
SET FILTER = G IN CAR OFF
The following commands activate some filters and deactivate others:
SET FILTER = UK LUXURY IN CAR ON
...
TABLE FILE CAR
PRINT COUNTRY MODEL RETAIL_COST
END
...
SET FILTER = LUXURY IN CAR OFF
TABLE FILE CAR
PRINT COUNTRY MODEL RETAIL_COST
END
The first SET FILTER command activates the filters UK and LUXURY, assigned to the CAR data source, and applies their screening conditions to any subsequent report request against the CAR data source.
The second SET FILTER command deactivates the filter LUXURY for the CAR data source. Unless LUXURY is reactivated, any subsequent report request against CAR will not apply the conditions in LUXURY, but will continue to apply UK.
To determine the status of existing filters, use
? FILTER [{file|*}] [SET] [ALL]]
where:
file
Is the name of a Master File.
*
Displays filters for all Master Files for which filters have been declared.
SET
Displays only active filters.
ALL
Displays all information about the filter, including its description and the exact WHERE/IF definition.
To query filters, issue the following command:
FILTER FILE CAR CLEAR
NAME=BOTH, DESC=Asian and British cars only
IF COUNTRY EQ JAPAN AND ENGLAND
END
SET FILTER =BOTH IN CAR ON
TABLE FILE CAR
PRINT CAR RETAIL_COST
BY COUNTRY
END
The output is:
COUNTRY CAR RETAIL_COST
------- --- -----------
ENGLAND JAGUAR 8,878
JAGUAR 13,491
JENSEN 17,850
TRIUMPH 5,100
JAPAN DATSUN 3,139
TOYOTA 3,339
The following is an example of querying filters for all data sources:
? FILTER
If no filters are defined, the following message displays:
NO FILTERS DEFINED
If filters are defined, the following screen displays:
Set File Filter name Description
‑‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
CAR ROB Rob's selections
* CAR PETER Peter's selections for CAR
* EMPLOYEE DAVE Dave's tests
EMPLOYEE BRAD Brad's tests
To query filters for the CAR data source, issue:
? FILTER CAR
If no filters are defined for the CAR data source, the following message displays:
NO FILTERS DEFINED FOR FILE NAMED CAR
If filters are defined for the CAR data source, the following screen displays:
Set File Filter name Description
‑‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
CAR ROB Rob's selections
* CAR PETER Peter's selections for CAR
To see all active filters, issue the following command:
? FILTER * SET
The output is:
Set File Filter name Description
‑‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
* CAR PETER Peter's selections for CAR
* EMPLOYEE DAVE Dave's tests
The asterisk in the first column indicates that a filter is activated.
When you report against a joined structure using a host file name, none of the original filters are active. Once the join is in effect, if you declare filters for the host file name, they apply to the joined structure and remain in effect as long as the join is in effect. When the join is cleared, the original filters for the host file are reactivated.
If you use the cross-referenced file name in a request, its activated filters are implemented. For related information, see Joining Data Sources.
‑*****************************
‑* JOIN AND FILTER INTERACTION
‑*****************************
‑* DECLARE A FILTER
FILTER FILE EMPLOYEE CLEAR
NAME=XXX WHERE JOBCODE EQ 'A01'
END
SET FILTER = XXX IN EMPLOYEE ON
‑* EMPLOYEE FILE SHOWS JOBCODE A01 ONLY
TABLE FILE EMPLOYEE PRINT EMP_ID JOBCODE
END
‑* ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
‑* NOW JOIN TO JOBFILE AND REDECLARE THE SAME FILTER WITH A DIFFERENT JOBCODE
‑* ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
JOIN JOBCODE IN EMPLOYEE TO JOBCODE IN JOBFILE
FILTER FILE EMPLOYEE
NAME=XXX WHERE JOBCODE EQ 'A07'
END
‑* (NOTE: NEW FILTER FOR JOIN STRUCTURE IS NOT ACTIVATED YET)
‑* EMPLOYEE FILE SHOWS **ALL** JOBCODES (ORIGINAL FILTER TURNED OFF BY JOIN)
TABLE FILE EMPLOYEE PRINT EMP_ID JOBCODE
END
‑* ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
‑* NOW TURN ON THE NEW FILTER THAT APPLIES TO THE JOIN STRUCTURE
‑* ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
SET FILTER = XXX IN EMPLOYEE ON
‑* SHOWS JOBCODE A07 (NOT A01) (NEW FILTER APPLIES TO JOIN ONLY)
TABLE FILE EMPLOYEE PRINT EMP_ID JOBCODE
END
‑* NOW CLEAR THE JOIN TO RE‑ESTABLISH THE ORIGINAL FILTER
JOIN CLEAR *
‑* NOW SHOWS JOBCODE A01 ONLY, AS BEFORE (ORIGINAL FILTER REACTIVATED)
TABLE FILE EMPLOYEE PRINT EMP_ID JOBCODE
END