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 = 'Caucasian' 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

An operator symbol that describes what is being done with the database field

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:

 

 

 

TelcoMgr Report File Names

 

The TelcoMgr Report Builder will attempt to save your new reports with a name similar to "RPT00001.RPT." The second report that you save will be named "RPT00002.RPT" and the third will be named "RPT00003.RPT" and so on for additional reports. Therefore, end-users are recommended to save report file names using the automatic schema with the built-in serialized report file name.

 

If you makes changes to one of the TelcoMgr standard reports which are installed with the application, then you should save the changes using "SAVEAS" and let TelcoMgr suggest the appropriate name with the serialized naming schema as described above. If you save your changes with the same name as used by TelcoMgr to store its standard reports, then your changes will be overwritten each time TelcoMgr is upgraded.

 

Optionally, you may create your own report file names. If you elect to save your reports with your own naming convention, for example "MyTelcoMgrReport01" then it is not necessary to add the ".RPT suffix" at the end of the file name. The Report Builder will append the suffix to your custom file name. Once again, end-users should not use the TelcoMgr report file naming convention to save new or modified reports.

 

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.

 

 

 

 

GOTO PAGE 12