Determine Hierarchy using Prompts Issue?

[bi]

I am trying to determine the hierachy using prompts.

I have 4 prompts

1.   Country   (Required)
2.   State       (Optional)
3.   County    (Optional)
4.   City         (Optional)

The following filter does not work unless all of prompts are required.

If ?City? is not null then Juridiction_Name = ?City?
Else if ?County? is not null then Juridiction_Name = ?County?
Else if ?State? is not null then Juridiction_Name = ?State?
Else Juridiction_name = ?Country?

Is there a workaround?  I cannot make any changes to the database and everything has to be handled through Report Studio.

[wyconian]

Hi

A filter has to be a boolean expression (something that evaluates to True or False) and can't include a conditional statement (basically you can't use an If Then Else statement in a filter).

Try something like this
(?City? is not null and jurisdiction_name = ?City?)
or
(?City? is null and ?County? is not null and jusidiction_name = ?County?)
or
(?City? is null and ?County? is null and ?State? is not null and jurisdiction_name = ?State?)
or
(?City? is null and ?County? is null and ?State? is null and jurisdiction_name = ?Country?)
or
jurisdiction_name = 'Other'
You may want to add a default value as well to make sure all bases are covered

Good luck

[bi]

I tried the filter given by wyconian but if the filter is set as optional then it appears to be completely ignored even if I have selected a field for State, County or City.

I even tried creating a new query item to determine what level of the hierachy was selected and the join against this query it the State/County/City filter automatically became required.