How-To: Write a query supporting a case-insensitive dashboard parameter?
Dashboard parameters, or user inputs, can be created by putting the
? character in front of the parameter
name withing your query. For example, ?username would
create a username input. These
are used within dashboards to allow user-configurable input parameters,
but they can also be used in queries. The inputs are case-sensitive by
default. This means that an input of
ADministrator would not match
administrator,
Administrator and so on.
The solution is to use the test() function and
compare everything in lower-case. For example:
// The input is "?MyInput" and we want to match it to "UserName".
// First we have to assign the input to a field, otherwise it doesn't work with test().
thisInput:=?MyInput
// Next we compare the two strings in lower-case to see if they're equal.
test(lower(thisInput) == lower(UserName))
Because we're comparing everything in lowercase, an input of
administrator would match
Administrator,
ADMinistrator,
AdMiNiSTRATOR, etc.