Tutorial - Page 11
Depending on how the person/company designed your database program, you may or may not be able to create FILTERS by using a separate module that is called prior to printing a report. Either way you can still create your own FILTERS for your reports.
A database FILTER is a means of extracting only a portion of information that you want. For example, if you only want to know the names of people living in France in your address book why would you print a list with EVERYONE's names? The list could be huge, and take forever to read through and extract only the information you want.
A database FILTER can be very simple (ex: Color = 'blue'), or complex (ex: Race = 'Caucasion' AND Income > 25000 AND (Status = 'Single' OR Status = 'Divorced') - it depends on what the situation requires. A FILTER is saved inside the report, so you don't need to create it each time that you want to print the report. There are some basic concepts that you need to understand before creating a filter; some knowledge of some of the Report Builder functions will also prove handy for creating filters.
If the person/company that designed your database program provides a Query By Example (QBE) module that is displayed before you print a report, you are in luck. Most of the QBE modules that are used are MUCH easier to use for generating a database filter; if this is the case then you can basically ignore this tutorial page, since I recommend using the QBE module instead of manually creating a filter. This page is only for those situations where a QBE module is not being used.
A simple definition of a FILTER could be described as comparing database information against something else. Some simple examples could resemble:
STATE = 'Ca'
PRICE > $300
AGE < 30
As shown above there are 3 parts to a filter:
The database field starts the filter
A text string or number that the database field is being compared against
You can retrieve the database field name from the Dictionary; the same goes for the different operators that you can use. The third part of the filter is up to you to decide - remember that if you are comparing database information against textual information you need to make it a string (enclosed in quote symbols); if comparing against a number you don't add the quote symbols.
Report FILTERS are created by selecting FILE from the pulldown menu; select Report Properties from the menu to display the Report Properties screen:
On the bottom of the screen is an entry field for the Record Filter; to the right of the entry field is the Dictionary pushbutton. The record filter is entered in this location, and once the SAVE button is clicked the FILTER is saved with the report.
Until you learn the different functions and operators that are built into the Report Builder you will rely heavily on the Dictionary for assistance in creating report filters. Let's start with a simple filter to show how to use the Dictionary to build the filter for us:
With your report open, and the Report Properties window visible (as shown in the above screen capture) click on the Dictionary button to display the list of database fields. We are going to select the STATE field in the dictionary, which inserts it into the entry field above. This completes step one of the filter building process
We now need to decide how we are going to compare the State field in the database. For this example we want to print out database entries for people living in Florida. To compare information we use an OPERATOR, which tells the computer how to manipulate information. After inserting a blank space after the field name we click on the Dictionary button again, and choose the entry titled "Equal To" from the long list of Standard Operators. This inserts an equal sign (=) after the field name, which completes step 2 of the filter.
Now we have to manually type in how we are going to compare the State database field. We wanted to print only those entries for Florida; the state database field is 2 positions in length, and stores the abbreviated state instead of the spelled out version. We will be comparing "FL" against the database field
Here is what the completed filter appears in the Report Properties screen:
In theory this filter is 100% correct; structurally it IS correct, but there exists a problem. The problem is how the State database field is stored in the database - when the user inputs the State information it is capitalized as "Fl", with the "l" being lowercase. The filter shown above is all uppercase. Because computers do EXACTLY what YOU tell them to do, the result will be that NO database entries will be found, even though there are 4 entries in this database that have people living in Florida!
Before throwing your hands in the air take a minute to compose yourself while we dig a little deeper into how filters work. The filter statement shown above is structurally correct: we have a database field, an operator and the text string for what it is being compared against. The problem is that we are telling the computer to make sure that the State field EXACTLY matches "FL". Computers are stupid - they don't understand that it should have checked for "Fl" instead. WE have to tell it to do that!
There are several ways to fix this problem; here is probably the easiest way of ensuring that a filter dealing with text strings will always work. It uses the upper() function in the Report Builder to convert the database information to uppercase temporarily, which will always accurately compare the database information against the text string. Armed with this knowledge we change the filter to look like this:
Now we can click the SAVE button, then run the report to see how the information looks:
Perfect! As mentioned before there is another way of changing the report filter to make sure that the data printed is correct. We would change the filter to perform two comparisons of the State database field against "FL" AND "Fl". You would piece together two different filters by using a CONDITIONAL OPERATOR out of the Report Builders dictionary. Here is what the revised filter would look like:
You may be asking yourself why even bother checking for database entries where the State database field is "FL", since the database program is supposedly capitalizing the information as the person types it in. The reason is simple - Murphies law! You never take for granted that information typed in by someone else is how you would type it in. The safest route is to check for all the possible ways that information could be typed in.