User login

Filters

Fine Tuning the Query


Once all of the fields have been added (with their names listed under Output Columns), it is time to design the specific attributes of the query by using sorts, filters, orders, and grouping. What follows is a discussion of the basic use of the different options available on the report edit page.


Filtering


To filter the records that appear in the report, first click the Filter tab. You will see a screen with all of the fields you’ve chosen for your report. Some of the areas on the screen include Column, Operator, Criteria, and Ask? Each row corresponds to a field. To add a filter, you must enter an operator and a value for criteria.


Filter Tab



Operator


Operators are selected by clicking on the drop-down list under to Operator. A brief explanation of the available operators is provided below. Anytime a “NOT” is contained in an operator, it reverses the way the selected operator is evaluated.

Equals:

The value entered must be an exact match to the value contained in the field. For example, if you enter the value “Employee,” the system would not find “Employees” as a match.

Greater than:

Tests for a value in the data field alphanumerically higher than the comparison value. In a test for alphabetic entries, such as WO Type, a higher value is one that comes after the entered value; a value that is later in the alphabet. When the data field's value is alphanumerically higher than the comparison value, the record is selected.

For example, selecting the field WO Type and the Value entry “PM” creates the expression “WO Type Greater than PM.” A record with a WO Type entry of “Safety” will be selected, but a record with a WO Type entry of “Corrective” or “PM” will not be selected.

Less than:

Tests for a value in the data field alphanumerically lower than the comparison value. In a test for alphabetic entries, such as WO Type, a lower value is one that comes before the entered value: a value that is earlier in the alphabet. When the data field’s value is alphanumerically lower than the comparison value, the record is selected.

For example, selecting the field WO Type and the Value entry “PM” creates the expression “WO Type Less Than PM.” A record with a WO Type entry of “Emergency” will be selected, but a record with a WO Type entry of “PM” or “Safety” will not be selected.

Less than or equal to:

Tests for a value in the data field alphanumerically equal to or lower than the comparison value.

For example, selecting the field WO Type and the Value entry “PM” creates the expression “WO Type Less Than Or Equal To PM.” A record with a WO Type entry of “Emergency” or “PM” will be selected, but a record with a WO Type entry of “Safety” will not be selected.

Greater than or equal to:

Tests for a value in the data field alphanumerically equal to or higher than the comparison value.

For example, selecting the field WO Type and the Value entry “PM” creates the expression “WO Type Greater Than or equal to PM.” A record with a WO Type entry of “Safety” or “PM” will be selected, but a record with a WO Type entry of “Corrective” will not be selected.

Like:

Like differs from Equals in the way it evaluates the data field. Like tests for a value in the data field that starts with the comparison value. If the characters of the comparison value match the starting characters in the data field, the record is selected.

For example, the filter WO Type like “P” will include records in which the WO Type entries all start with “P,” such as “PM” and “Project.”

Contain:

Tests for the existence of the comparison value anywhere within the data field. If the data field contains the comparison value, the record is selected.

For example, suppose you wanted to find out how many people have reported that the temperature was too hot in their office. The expression “WO Description Contains Hot” will include records in which the WO Description entries include “Hot” anywhere in the WO Description: “my office is too hot,” “temperature is too hot,” “it’s hot in here,” etc.

Value is in a range:

Tests for a value in the data field alphanumerically between the two comparison values. This is often used for date fields. For example if you wanted to see all of the work orders created in May of 2004, the range is 05/01/2004 through 05/31/2004.

Not:

Not included in any operator reverses the way the selected operator is evaluated. When the data field and the comparison value match, the record is not selected.

For example with a Not Like operator, selecting the field WO Type and the Value entry “PM” creates the filter “WO Type Not Like PM.” Each record with the WO Type entry of “PM” will not be selected. Records with WO Type entries of any other WO Type will be selected.


Criteria


When entering the criteria, you will either have a blank line in which to type the value or a lookup from which to select it. The choice will depend on how that field is configured in the system. There are two options for entering the criteria: entering a value or entering an expression. The first, entering a value, can be done from either the main Filter screen or from the Advanced Filtering screen, while entering an expression can only be done through the Advanced Filtering screen. To access Advanced Filtering, click the icon to the right side of the screen, for that row.

Advanced Filtering Button

Within that screen, the space for entering a value, which is marked Criteria, will have an fx symbol next to it. Clicking on that will take you to the Expression field, which will have a pencil symbol next to it. These two icons are for switching from one mode to the other. NOTE: If you are not familiar with SQL code, do not attempt to use the expression builder.
After entering the information for the filter, click the Save button.

The Function box allows for some additional configurations to be placed on the field. These functions include but are not limited to the following:

    Upper: Forces all characters to upper case
    Lower: Forces all characters to lower case
    Proper: First letter is upper case and the rest is lower case
    RTrim: Removes spaces from the right of the operand
    LTrim: Removes spaces from the left of the operand
    AllTrim: Trim all spaces from both sides of the operand
    Empty: Checks to see if the field is empty

This section is used more in advanced reporting. If you would like a detailed discussion on ways in which to use these Function options, please consider taking that course.


Ask at runtime


If you would like to change the value of the filter for this field each time this report is run, click the Ask? button. There is no need to enter Criteria when using this option, only an Operator. You will enter the criteria when you run the report.

Example:

If we only want to see records where the WO Type is “CORRECTIVE,” we would choose “equals” for the Operator and “CORRECTIVE” for the Criteria. Click the Save button to finalize the changes. This report will return all of the records (work orders) where the WO type field is “CORRECTIVE.”


Filter Example


Alternatively, we can choose to have the system ask us what we want to search for when we run the report. To do that, simply click the Ask? Button and it will turn red, indicating that the setting is on. You can leave the Criteria blank. Click the Save button.


Filter Example 2


When we run this report, because Ask? Is turned on, a window will appear, asking us in specify the value for which the report should search.


Adding Filter Fields

You can add fields to this screen, in order to create more filters. To add a field, click on the folder icon for the appropriate table. A field selection window will come up, showing the fields for that table, just like when you selected the fields in the Columns screen. Check the boxes for the fields you wish to add and click the Save button to add them. These fields that you add this way will not necessarily be displayed on the report, but they will now be available for creating filters.

Filter Tab - Add Fields


Removing Filter Fields

You can also remove fields that you don’t want on the filter screen, by clicking the trashcan icon to the right of the row for that field.

Delete Filter