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.
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 aNOT 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.
Greaterthan:
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 WOTypeGreaterthanPM. 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.
Lessthan:
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 WOTypeLessThanPM. 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.
Lessthanorequalto:
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 WOTypeLessThanOrEqualToPM. 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.
Greaterthanorequalto:
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 entryPM creates the expression WOTypeGreaterThanorequaltoPM. 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 WODescriptionContainsHot will include records in which the WO Description entries include Hot anywhere in the WO Description: myofficeistoohot, temperatureistoohot, it’shotinhere, etc.
Valueisinarange:
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 WOTypeNotLikePM. 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 AdvancedFiltering screen, while entering an expression can only be done through the AdvancedFiltering screen. To access AdvancedFiltering, click the icon to the right side of the screen, for that row.
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.
Askatruntime
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.
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.
When we run this report, because Ask? Is turned on, a window will appear asking us to specify the criteria which the report should search.
AddingFilterFields
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 theColumnsscreen. 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.
RemovingFilterFields
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.
