In the previous help topic some very simple filters were displayed. 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 (or your sales staff) 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 the clerk instead typed in 'BURBANK'?

 

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'!

 

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 click HERE.

 

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 Capslock key can cause a great deal of problems when you assume!

 

Goto the next topic