Filter Records
When you run a report, you often do not want every available record displayed or printed. Selecting a subset of records to work with is called filtering. The Filter page of the Reports Explorer and the report wizards allows you to specify which records are included in the report.
This page may not be available for some reports.
Filter Page
This Filter page has the following options:
Use same filter next time report is run: if this option is turned on, any filter specified is used as the default filter the next time the report is selected. Of course, the filter can be changed the next time, but this option is handy if you usually use the same filter each time the report is run.
Filter: click this button to display the Filter dialog. This dialog allows you to determine which records to include in the report. The space below the Filter button shows the filter specified for this report.
Warning if no filter when report is run: if you run a report without creating a filter for it and there are a lot of records in the tables in that report, it can take a long time to run the report. To prevent this, Student Manager Reports warns you that there is no filter for the report and gives you the option of running the report anyway or canceling. If you don't want to see this warning message when you run this report without a filter, turn the Warning if no filter when report is run setting off. You can set the Default warning if no filter when a report is run setting as desired in the Options dialog to control this for all new reports.
Exclude: click this button to display the Exclude dialog. This dialog allows you to determine which records to exclude in the report. The space below the Exclude button shows the exclusion filter specified for this report.
A filter is made up of one or more conditions. A single condition compares a field to a value. For example, "Order Amount is greater than $100.00" is a condition that only allows those records with an order amount of more than $100.00 to appear in the report.
A condition is made up of either three or four parts, depending on whether it's the first condition in a filter or not: the field to filter on (for example, Order Amount), the operator, the value or values, and the connection.
Filter Operators
How a field is compared to the value is called the operator. There are several operators available; they are shown in the table below. Some operators use more than one value; for example, is between requires two values (as in "Order Date is between 01/10/96 and 12/31/96"). Other operators do not use any value; for example, "Zip code is blank" is either true or not without needing a value.
| Operator | Description |
|---|---|
| equals | The data stored in the field must exactly match the value you specify. Example: Last Name equals Johns The report shows only records with "Johns" as the last name. Records with "Johnston," "Johnson," and "Johnstone" are not included. |
| does not equal | The data stored in the field may be anything but the value you specify. Example: City does not equal Calgary |
| begins with | The data in the field starts with the value specified. Example: Last Name begins with Johns The report shows records with "Johns," "Johnston," "Johnson," and "Johnstone" as the last name. This operator only appears for character and memo fields. |
| does not begin with | The data in the field starts with anything but the value specified. Example: Last Name does not begins with Johns The report shows records other than those with "Johns," "Johnston," "Johnson," and "Johnstone" as the last name. This operator only appears for character and memo fields. |
| ends with | The data in the field ends with the value specified. Example: Last Name ends with son The report shows records with "Johnson" and "Clarkson" as the last name. This operator only appears for character and memo fields. |
| does not end with | The data in the field ends with anything but the value specified. Example: Last Name does not end with son The report shows records other than those with "Johnston" and "Clarkson" as the last name. This operator only appears for character and memo fields. |
| contains | The field has the specified value anywhere in the data it contains. Example: Outline contains Federal Gov All records with "Federal Gov" somewhere in the Outline field are included. This operator only appears for character and memo fields. |
| does not contain | The field does not have the specified value anywhere in the data it contains. Example: Outline does not contain Federal Gov This operator only appears for character and memo fields. |
| is blank | Includes all records where the specific field is empty or contains an unknown (null) value. Example: ZipCode is blank |
| is not blank | Includes records where the specific field contains data. Example: ZipCode is not blank |
| is known | Includes records where the specific field has any value. Example: ZipCode is known |
| is unknown | Includes records where the specific field has an unknown value, sometimes known as "null." Example: ZipCode is unknown |
| is greater than | Includes records where the specific field contains a value in excess of the specified value. Example: Amount is greater than 100 Example: City is greater than M |
| is greater than or equal | Includes records where the specific field contains a value in excess of or equal to the specified value. Example: Amount is greater than or equal to 100 Example: City is greater than or equal to M |
| is less than | Includes records where the specific field contains a value under the specified value. Example: Amount is less than 100 Example: City is less than M |
| is less than or equal | Includes records where the specific field contains a value under or equal to the specified value. Example: Amount less than or equal to 100 Example: CITY is less than or equal to M |
| is Yes | Includes records where the specific field contains "Yes." Example: Married is Yes This operator only appears for logical or Boolean fields. |
| is No | Includes records where the specific field contains "No." Example: Married is No This operator only appears for logical or Boolean fields. |
| is between | Includes records where the specific field contains values in a specified range. Example: Amount is between 150 and 250 Example: Date Started is between 05/01/94 and 12/31/94 |
| is not between | Includes records where the specific field does not contain values in a specified range. Example: Amount is not between 150 and 250 Example: Date Started is not between 05/01/94 and 12/31/94 |
| is one of | Includes records where the specific field contains one of up to ten specified values. The equivalent expression would be created using ten separate "equals" conditions. Example: City is one of Tokyo, Calgary, New York |
| is not one of | Includes records where the specific field does not contain one of up to ten specified values. The equivalent expression would be created using ten separate "does not equal" conditions. Example: City is not one of Tokyo, Calgary, New York |
| is | This operator, which only appears for date fields, allows you to choose a descriptive name for a date range. For example, if you choose "is" for the operator and "This Month" for the value, the filter condition includes records where the field is between the first and last days of the current month. Example: Order Date is This Year |
Filter Connection
Conditions are connected together with a connection. The connection can be either AND or OR. The AND connection means the two conditions it connects must both be true for a record to be included in the report, while the OR connection means that either one or both can be true for a record to be included.
You can think of AND as "more restrictive." For example, if the first condition is "City equals San Francisco," that gives a certain set of records. Adding a second condition such as "Order Amount is greater than 100.00" using the AND connection means that for any given record, the city must be San Francisco and the order amount must be greater than $100.00. Since that excludes San Francisco records with an order amount of less than $100.00 (which just specifying the first condition includes), these two conditions together are more restrictive (there are fewer records in the report) than either of these conditions alone.
You can think of OR as "less restrictive." For example, if the first condition is "City equals San Francisco" and you add a second condition such as "City equals Los Angeles" using the OR connection, this means that for any given record, the city can either be San Francisco or Los Angeles. These two conditions together are less restrictive than either of these conditions alone, since the report includes records matching both conditions.
Notice the use of AND and OR with filters can be the opposite of how these words are used in English. For example, someone might say "print out all of our customers from San Francisco and Los Angeles." You might think you use the AND connection in this case, but in fact you use the OR connection since you want records where the city is either San Francisco or Los Angeles. The way to think of it is this: for each record, check each condition. It is not possible, for example, for the city for any one record to be both San Francisco and Los Angeles at the same time. It could be either one, however, so OR is the correct connection to use.
Watch out if you need to use both AND and OR connections in a filter. Conditions connected with AND are evaluated before those connected with OR. For example, if you want all customers from San Francisco or Los Angeles where the sales amount is more than $50, you might think you could use "City equals San Francisco or City equals Los Angeles and Sales is greater than 50." However, this likely won't give you the results you want because the AND is evaluated first. So, this gives all customers from Los Angeles where the sales amount is more than $50 (the second and third conditions ANDed) or those from San Francisco regardless of the sales amount (the first condition).
If you need to use both AND and OR connections in a filter, you likely need to use parentheses with one or more of them. Parentheses determine the order of evaluation because conditions in parentheses are evaluated before anything else. So, to get all customers from San Francisco or Los Angeles where the sales amount is more than $50, use "(City equals San Francisco or City equals Los Angeles) and Sales is greater than 50." The way you specify the parentheses is to select ") AND" as the connection for the last condition (you don't have to specify the opening parenthesis; Student Manager Reports inserts that automatically).
Here's a more complex example. A AND (B OR C) AND D means evaluate B OR C first (let's call the result E), then do A AND E (let's call that F), and finally F AND D. If the parentheses were left out, it evaluates A AND B (let's call that E), then C AND D (let's call that F), and finally E OR F.
Filter Dialog
The Filter dialog allows you to see the conditions that make up the filter, add new conditions, and edit or remove conditions. You may create a filter for a single report or you may create and save it for use with other reports.
The Filter dialog has buttons that provide functions such as adding conditions, deleting conditions, etc. You can also choose these functions by right-clicking the dialog and choosing the appropriate function from the shortcut menu that appears.
Adding a Condition
To add a new condition to the filter, click the Add button. You can also press the INSERT key or right-click and choose Add Condition from the shortcut menu. If you don't have any conditions in your filter yet, the Filter Condition dialog shown below appears.
If you already have at least one condition in your filter, the Filter Condition dialog appears a little differently.
The Connection drop-down list, which does not appear if this is the first condition for your filter, is used to define how this condition is connected to the previous condition in your filter. The default connection is and, but you can also choose or or one of various combinations of and or or with parentheses to allow you to group your conditions and specify the order in which the conditions are evaluated. Student Manager Reports automatically balances parentheses, so there is no need to worry about a starting or ending parenthesis.
The Table drop-down list shows the tables you can select fields from. The Field drop-down list shows the fields you can filter on from the selected table. The Show all fields option determines which tables and fields you can select. Initially, this option is turned on, so you can see all tables and fields. Turn this option off if you only want to see tables and fields that actually appear in the report.
After selecting a field, select a comparison operator from the Operator drop-down list. The options that appear in this list depend on the type of field selected. For example, for logical or Boolean fields, the only choices that appear are is Yes and is No.
Once you've selected an operator, you may enter a value to compare to the field. You won't be allowed to enter a value if you choose the is blank, is not blank, is known, is unknown, is Yes, or is No operators. If you choose the is between or is not between operators, you must enter two values. If you choose the is one of or is not one of operators, you can enter up to ten different values.
If you're not sure what values appear in the field or want to select a value without having to type it, click the Values button. A list of unique values from the field appears in the Select Values dialog. You can choose a single value or multiple values; to select more than one value, hold down the Ctrl key as you click the desired values. If you choose more than one, the operator is automatically changed to is one of. To search the list of values, type something in Search; as you type, the list jumps to the first entry containing what you typed. Press F3 to jump to the next entry. Search is only available for character values.
If you select a date field, you can either type the desired date or click the down arrow at the right of the text box to display a calendar. Whether the calendar is displayed or not, you can press one of the following keys as shortcuts:
| Key | Purpose |
|---|---|
| Ctrl-Home | Today's date |
| Home | First day of the month |
| End | Last day of the month |
| PgUp | Previous month |
| PgDn | Next month |
| Ctrl-PgUp | Previous year |
| Ctrl-PgDn | Next year |
| Space or F4 | Toggle the calendar (displayed or not) |
| Esc | Close the calendar if it's open |
| Up Arrow | Next month, day, or year, depending on which is selected (when the calendar is open, the previous week) |
| Down Arrow | Previous month, day, or year, depending on which is selected (when the calendar is open, the previous week) |
| Left Arrow | When the calendar is open, the previous day |
| Right Arrow | When the calendar is open, the next day |
When the calendar is displayed, you can select a date by simply clicking it. Click the left or right arrows at the top of the calendar to move to the previous or next month. Click the month/year to display a list of months you can select from. In the month view, click the year to display a list of years to choose from. In the year view, click the decade at the top to display a list of decades to select from. To select today's date, click the word "Today."
Date fields have a special operator other types of fields don't have: is. This operator allows you to choose a descriptive name for a date range. For example, if you choose "is" for the operator and "This Month" for the value, the filter condition includes records where the field is between the first and last days of the current month.
If you select a field with a pre-defined list of values, a drop-down list of those values appears.
If you select a character or memo field, a Case sensitive setting may appear. If you turn this setting on, only those records with the value in the same case (that is, upper and lower case characters) as you typed it are included. If it isn't turned on, case doesn't matter; for example, entering "Jones" matches "Jones," "JONES," "JoNeS," or any other combination of case.
To compare a field to another field rather than a value, select "Field" from the Compare to option to use a field (the default is Value, which means a value is used). Select the field to compare against in the drop-down list that appears. Note that this only allows you to select fields that have the same data type and are from the same table as the field you're filtering on or are in the report. To compare a field in one table to a field in another table that isn't in the report, use an expression (described next) and specify the name of the field in the other table as the expression.
To compare a field to an expression, select "Expression" from the Compare to option. Enter the expression into the text box. For example, to look for all records with a date field falling in the last week, use the "is between" operator and DATE() - 7 for the first expression and DATE() for the second expression. Since DATE() gives today's date, DATE() - 7 is seven days ago, so this gives all records with the date being between one week ago and today. You can also click the Expression Builder button (the button labeled "..." beside the text box) to display the Expression Builder, which is a much easier way to create the expression to use.
If you want to be prompted to enter the values when you run the report, turn on the Ask at runtime option. If Compare to is set to "Expression," the value of the expression is used as the default for the ask-at-runtime value. Otherwise, the value saved with the condition is used as the default.
By default, all filter conditions are displayed in the header of a report if the Include filter in report header option in Step 5 of the report wizards is turned on. However, if you don't want a particular condition to be included, turn off the Include in filter display setting. This is particularly useful for reports that have a lot of filter conditions, which can get quite long when listed in the report header. You might in that case turn this option off for all but ask-at-runtime conditions.
A filter condition is displayed in a report as "Field operator value", such as "Status equals Active." If you want something more descriptive, enter a custom description for the condition, such as "Active Customers;" anything you put in Custom description is displayed instead. If you want to use an expression rather than fixed text, such as a user-defined function, add "{" and "}" (without the quotes) around field or function names in the text. Specify {Value} as a placeholder for the filter condition value or, for the "is between," "is not between," "is one of," or "is not one of" operators, {Value1} for the first value, {Value2} for the second, and so on. Click the Expression Builder button to display the Expression Builder to make it easier to enter field or function names.
Here's an example of an expression that displays "Sales from date to date" (such as "Sales from 01/01/2018 to 12/31/2018") for the filter condition:
Sales from {Value1} to {Value2}
After you've completed entering the condition, press the OK button to save the condition and add it to the list. Choose Cancel to cancel the new condition.
If you don't enter a value to compare to the field, the operator is automatically changed to is blank.
Edit a Condition
To edit a condition, select it in the list and click the Edit button, or press ENTER or double-click the condition. You can also right-click and choose Edit Condition from the shortcut menu. The Filter Condition dialog appears, and you may change any of the items that make up the condition. Click the OK button to save any changes you made to the condition or Cancel to cancel any changes.
Delete a Condition
To delete a condition, select it in the list and choose the Delete button. You can also press the DELETE key or right-click and choose Delete Condition from the shortcut menu. The list adjusts as necessary.
Rearrange Conditions
A button with up and down arrows appears at the left edge of each condition in the list. You can click this button and drag the selected condition up or down in the list. This is only necessary if you use parentheses to group conditions and wish to rearrange the grouping.
Clear Filter
The Clear button deletes all conditions in the filter. You can also right-click and choose Clear Filter from the shortcut menu.
Counting Matching Records
The Count button displays the number of records matching your filter. You can also right-click and choose Count Records from the shortcut menu.
Saving, Retrieving, and Deleting Filters
Because filters can be complicated, you can save and retrieve commonly used filters. A saved filter can also be used as the basis for a new, slightly different filter. If you retrieve a stored filter and make changes to it (perhaps adding a new condition or changing an existing condition), you may re-save it with the same or a different name.
Clicking the More button expands the filter dialog. Choosing this button again (which then appears as Less) restores the dialog to its former appearance.
The Store button is enabled when you have at least one condition defined for your filter. When you click this button, or right-click and choose Store Filter from the shortcut menu, a dialog appears which allows you to enter a name for your filter. You might want to include your own name in the filter name so others can tell whose filter it is. A name like "Ron's Filter - Jan/94" might be appropriate. You can also enter a more complete description of the filter if you wish. Type in the name and description and click the OK button. The name of the filter appears below the Store button.
To retrieve a filter, click the Retrieve button or right-click and choose Retrieve Filter from the shortcut menu. A list of stored filters is presented. Select the one you wish to retrieve and press the OK button to load the saved filter. The name of the filter appears below the Store button and the filter conditions display in the list.
To delete a saved filter, press the Remove button or right-click and choose Remove Filter from the shortcut menu. A list of saved filters is presented from which you can select the one to delete. Click OK to delete the selected filter.
Ask at Runtime Filter Conditions
When you run a report that has any filter conditions with the Ask at runtime option turned on, you are prompted to enter the values for those conditions.
Each condition appears in its own step in the dialog that appears. After entering the value for one condition, you can select the next one by clicking the Next button or select the previous condition by clicking the Back button. You can also jump to a particular condition by selecting it from the drop-down list at the top of the dialog.
If you want a condition to be ignored for this run of the report, turn on the Ignore this condition option. This will act as if the filter condition does not exist.
When you have entered the values for each of the conditions, click OK to continue with the report. The OK button is disabled until you have selected the last condition. If you click Cancel instead, the report will not continue.
When you run a batch report, it prompts you for all unique ask-at-runtime filter conditions for all reports at the start of the run and uses those values for the conditions in all reports. That way, you aren't asked more than once for the same value.
Excluding Records
The Filter button in the Filter page of the Reports Explorer and the report wizards allows you to determine which records to include in the report. The Exclude button allows you to specify which records to exclude from the report. We call this a "negative" query because it looks for the absence of data, such as all customers you didn't contact in 2007 or all customers who didn't buy Widgets.
You may think that the way to do this is to filter on Contact Date is not between 01/01/2007 and 12/31/2007 or Product Name is not equal to Widgets. However, this will not give the results you expect for two reasons:
If you contacted the customer in 2006 or 2008, you'll get those records since they match the condition Contact Date is not between 01/01/2007 and 12/31/2007. However, that doesn't mean you didn't contact them in 2007. Similarly, you'll get all customers who bought anything other than Widgets but that doesn't mean they didn't buy Widgets at all.
Because of the way database queries work, the absence of data means no records are retrieved using a normal filter condition. For example, if there are no activity records for a customer in 2007, there is nothing to retrieve from the database, so no customers are displayed.
The solution is to use an exclude filter. This tells Student Manager Reports to exclude those records matching the exclude conditions. For example, creating an exclude filter on Contact Date is between 01/01/2007 and 12/31/2007 means you want to exclude customers who have an activity record in 2007, which therefore gives you those customers who did not have an activity record in 2007. To get customers who didn't buy Widgets, use an exclude filter of Product Name equals Widget. This excludes those customers who bought Widgets, so it gives you everyone who didn't buy Widgets.
The way an exclude filter works is that it first takes all records, then it removes those matching the exclude conditions, leaving the rest, which obviously don't match the conditions.
Creating an exclude condition is just like creating a normal filter condition. The Exclude and Exclude Condition dialogs work exactly the same as the Filter and Filter Condition dialogs. The only difference is that the conditions you specify for an exclude filter are used to exclude records from the query rather than include as a filter would normally do.
Note that exclude conditions shouldn't be used in place of a normal "not equals" filter. For example, if you want a list of customers who aren't in Germany, use a normal filter condition of Country does not equal Germany rather than an exclude condition of Country equals Germany. You will get a warning if you try to create an exclude condition of this type, although that doesn't prevent you from using it.
Also note that exclude conditions usually involve tables that don't appear in the report. For example, if you want a list of customers who haven't bought anything in the last 90 days, you would select the appropriate fields from the Customers table and no fields from the Orders table, but the exclude filter would be on Order Date from the Orders table.