Tutorial - Lesson 10

  Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic! Mail us feedback on this topic!  

 

You can create FILTERS by using a separate module that is called prior to printing a report. Or, you can 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 Los Angeles in your database, why would you print a list with everyone's name? 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.

 

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 three 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 apostrophe symbols); if comparing against a number you don't add the apostrophe symbols.

 

Report FILTERS are created by selecting File from the pulldown menu; then select Report Properties from the menu to display the Report Properties screen:

 

RBDetail25

Report properties page and Record filter

 

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 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) clear the Res:RespondentIDNumber < 50 text, and click on the Dictionary button to display the list of database fields. We are going to select the Zip Code field in the dictionary, then double-click it with the left mouse button, 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 Zip Code field in the database. For this example we want to print out database entries for people living in a specific Zip Code.  We're going to use '90003' for our database. (To be able to view results from your own database, substitute a residential Zip Code from your own market for this example). 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 Sign" from the long list of Standard / Operators / Logical. This inserts an equal sign (=) after the field name, which completes step 2 of the filter. (Once you're familiar with the operators and field names, you may type them directly into Report Builder properties screens, without having to select them from the dictionary list.)

 

Now we have to manually type in how we are going to compare the Zip Code database field. We wanted to print only those entries for 90003; the zip code field is a number (not a text string), so we don't need to use the apostrophe around it.

 

 Here is what the completed filter appears in the Report Properties screen:

 

RBDetail18

Record Filter for ZipCode = 90003

 

Comparing numbers in a Filter, such as a Zip Code, is easy, because there is only one way to type a number.  But what if we were filtering by State?  If we were looking for only people that lived in Florida, we would probably use a filter such as:  Res:State = 'FL'.  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 example, Res:State = 'FL' 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 might be many 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 Res:State = 'FL' 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 arbitrary - they don't understand that it should have checked for "Fl" as well as "FL". 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 you would change the filter to look like this: Upper(Res:State) = 'FL'

 

In this case, we are sorting by Zip Code (numeric), so we don't have to worry about the Upper() function for this report.  Click the Save button, then run the report to see how the information looks:

 

RBReport5

Sample report of only respondents in Zip Code 90003

 

Perfect!

 

To recap: in this section you learned:

 

What a filter is, and why one would be used

 

How a filter is structured (what 3 components make up a filter)

 

How to use the Report Builder features to easily create a simple filter

 

How to create a filter that will work 100% of the time without worrying how the database information is structured (lowercase, uppercase, proper case)