Optional Parameters and Optional Detail Filters?

[cchang]

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?

[dougp]

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

[dougp]

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

Code Select Expand

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.