ssrs report 2008 pick 1 or other parameter

Question

In an ssrs 2008 report, I have a user that would like to select 2 different parameters sometimes.

Some times the user would like to: 1. select only zip code, 2. select only city and state, or 3. select both zip code and city-state combination.

In SSRS 2008 reports, I do not see how this is possible? If I am incorrect would you let me know how to accomplish this goal?

If this is not possible, the only option I can see is to create another parameter where the options would be to select: 1. select only zip code, 2. select only city and state, or 3. select both zip code and city-statement combination. Once this option is selected, then the appropriate option would be to: have the requested parameters be available to request.

Let me know what you suggest would be the best option.

Answer 1

I would have two filters (actually the City and State would be separate, so THREE) and allow them to be NULL.

If you want to filter by your parameter in the query, you would add to you where clause to match the parameter unless it is NULL:

SELECT * 
FROM TABLE 
WHERE (ZIP = @ZIP OR @ZIP IS NULL)
   OR (
       (CITY = @CITY OR @CITY IS NULL)
      AND
       (STATE = @STATE OR @STATE IS NULL)
      )

If you want to filter in SSRS using an Expression, you would need to combine them in an IIF:

=IIF(Fields!ZIP.Value = Parameters!ZIP.Value AND NOT ISNOTHING(Parameters!ZIP.Value), 1,   
 IIF(Fields!CITY.Value = Parameters!CITY.Value AND NOT ISNOTHING(Parameters!CITY.Value) AND Fields!STATE.Value = Parameters!STATE.Value AND NOT ISNOTHING(Parameters!STATE.Value), 1, 0)) 

And set the value to match as 1.

This reads as

If the ZIP equals the ZIP parameter and the ZIP parameter is NOT NULL then YES

Else

If the City and State fields equal their parameter and aren't NULL then Yes

Else NO.