Dashboard prompt that can handle upper or lower case input

Question

I have a dashboard that has several prompts that users may often drop values into (rather than going through the dropdown to select each value). For the purpose of this post, I'll just focus on 1 of the prompts (as they will all pretty much behave the same). That prompt will be for 'Address'.

Being that I am not sure what case the users will always enter the values, I'd like for my prompts to handle both upper & lower case inputs. My thinking for this was to:

(1) Create a variable prompt that populates a variable called 'vAddress'

(2) Have a filter (using the 'Convert this filter to SQL' option) in the report that is:

upper("Location"."Address") IN (upper(@{vAddress}))

Unfortunately, the above does not work. I get an error.

I also tried using the following for the filter:

upper("Location"."Address") IN (upper(cast(@{vAddress} as CHAR)))

This also returns an error.

Is there a better way of doing this?

Any help is greatly appreciated.

Gianni Ceresa · Answer

Hi,

I have the feeling you just have a problem with your logic ....

It makes a lot of AND and not a single empty / default value managed.

If your presentation variable don't have a value they actually match only the rows of your tables where the values are empty, and all the conditions are linked together by AND, so it's really super strict as filter ...

Also the IN require the values to match perfectly the string in your variables.

With that kind of filter you must enter all the values perfectly matching the value for all the variables or it will always return nothing ...

Have a look at your physical SQL, you will see the filter added to your query just can't match any data.

Chris Arnold · Answer

Thanks for the reply Gianni.

Adding the quotes definitely gets the errors to go away, so that is good.

BUT...the filters still are not working. Putting the quotes in causes the filter to return no rows -- regardless of case.

Below you can see where I enter the state as 'OH' -- which I know should return rows (I've tested without using the upper function and it returns rows). But with the quotes, it does not return any rows.

At this point I'm thinking I can make it work by just having filters something like:

(upper("Location"."Address1") IN (@{vAdd1})

or "Location"."Address1" IN (@{vAdd1}))

and

(upper("Location"."Address2") IN (@{vAdd2})

or "Location"."Address2" IN (@{vAdd2}))

and

(upper("Location"."City") IN (@{vCity})

or "Location"."City" IN (@{vCity}))

and

(upper("Location"."State") IN (@{vState})

or "Location"."State" IN (@{vState}))

I haven't tried that yet, but I think it might work.

It certainly doesn't have the versatility that I was hoping for, but at least it will handle if the users enter things in all caps

Gianni Ceresa · Answer

Chris Arnold wrote:Unfortunately, the above does not work. I get an error.This also returns an error.

First would be easier if you also post the errors you get as it avoid people to guess it ....

Going back to your filter: upper("Location"."Address") IN (upper(@{vAddress}))

Did you try adding '  ' all around @{vAddress} ?

So something like: upper("Location"."Address") IN (upper('@{vAddress}'))

Because your variable is replaces by its value, but no ' ' is added around, so your formula end up looking like upper(my address) which isn't the same as upper('my address)'