I have a prompt page where i have optional 2 queried search and select parameters: ?State?, ?City?. This allows my users to select any state or any city, or skip it all and show everything.
On my query i am using an optional detail filter:
[State] in (?state?)
or
[City] in (?city?)
If the user doesn't make any choices then it shows the full list of places with the state + city info.
If the user selects states and cities then it will show the list of places with any states from selection and any cities from selection.
But if the users only select states or cities the detail filter doesn't work, and it returns all.
I tried to split the optional detail filters into 2, 1 for city and 1 for state. Which will work when only 1 option is used, but then if they use both creates a requirement that the state must have the city.
Is there a way i create a filter where i can check which parameter has data and then write out a case statement for each scenario? I tried to use ?city? is null, ?city? is empty, ?city? = '', but none don't work. Or does this have to be achieved through some other method?
If you only select states, your logic is
[State] in ?state?
or
[City] in <anything>
So you should get everything.
I think the result you want should be
[State] in ?state?
or
[City] in ('this is not a city')
Your filtering system will need to be considerably more complex. I think this is largely because you're using in. It would be easier with =.
Using GO Sales as an example:
filter in output query
case
when '(don''t use)' in ?state? then 'this is not a state'
else [Province or state]
end
in ?state?
or
case
when '(don''t use)' in ?city? then 'this is not a city'
else [City]
end
in ?city?
Queries:
a
name = '(don''t use)'
order = 0
c
City = [Sales (query)].[Branch].[City]
order = 1
s
State = [Sales (query)].[Branch].[Province or state]
order = 1
City = c UNION a
State = s UNION a
Prompts:
State
query = State
order by order, State
City
query = City
order by order, City
Filter and prompts are required.