0123 - User-Selectable Filters (DB Connection)
Description:
How to create user-selectable filters for the Database Connection.
How to create user-selectable filters for the Database Connection.
When using the Database Connection, sometimes it is desireable to let the user select among a number of predetermined filters. For example, a filter by month where the user can select which month's data they wish to retrieve. This computation explains how this can be accomplished.
Problem: Reason says that you should be able to use a multiple choice variable for the filter. However, a multiple choice variable will cause this error:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1
The solution requires a computation that SETs a text variable to the filter value. We will call the variable Filter-t. Use this variable as the Database Connection filter:
(your database field) EQUALS Filter-t
Next, create a multiple choice variable called Filter-m. Each filter value you wish to provide should be made an option for Filter-m. The variable must be se to "Select one only."
Finally, create a computation variable called Filter-c with this script:
SET Filter-t TO "«Filter-m»"
Use Filter-m to choose the appropriate option from a lookup list. Use Filter-c to set the value of Filter-t. (NOTE: Make sure you use the chevrons within quotes, otherwise, you will get an error.) And, as mentioned, use Filter-t as the actual filter.