Filters 101 - Part 4

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

 

In the previous topic some very simple filters were demonstrated. While structurally correct, they could use some modification to make them 100% reliable. You never know exactly how database information is stored; some database programs have very strict safeguards incorporated in them to ensure data is highly structured. Even then it is better to think defensively and create database filters that will work correctly in any situation.

 

Let us assume that a database program tracks customer sales. The data entry screen allows you to type in the customer name, their address, etc. Naturally the customer information is text information (A-Z, a-z); let us assume that the boss wants a report printing all customers living in Burbank; a simple filter should work:

 

CUS:City = 'Burbank'

 

The filter above looks correct, and would work IF the data entry clerk ALWAYS typed in the city 'Burbank' the same way every time. What would happen if the CapsLock key was turned on and one person typed in 'BURBANK' instead?

 

If you guessed that the filter wouldn't work, you would be correct! That is because the comparison of the database field CUS:CITY must EXACTLY match the text string 'Burbank'! To a computer, 'Burbank' and 'BURBANK' are two totally different things!

 

There are several ways of fixing this common problem. If you wanted to create a lot of work for yourself you could define the filter like this:

 

CUS:City = 'Burbank' or CUS:City = 'BUrbank' or CUS:City = 'BURbank' or CUS:City = 'BURBank' or CUS:City = 'BURBAnk' or CUS:City = 'BURBANk' or CUS:City = 'BURBANK'

 

Thankfully, there is a much easier way of fixing it than this!! By using the functions  UPPER() or LOWER() you can define the filter like this instead:

 

upper(CUS:City) = 'BURBANK'

  -or-

lower(CUS:City) = 'burbank'

 

Either way is guaranteed to work 100% of the time. You will also greatly increase the operational speed of the filter by using the upper() or lower() functions instead of using the very long filter example. To learn more about the built in functions in the Report Builder, see that section.

 

The important thing to consider here is this - never take for granted that database information will be typed in exactly as you think it is. The CAPS lock key can cause a great deal of problems when you assume!

 

See also

Dictionary - Functions