MS Reporting 2010: Use Multi-Value Drop-Down to Add/Subtract WHERE clause from SQL Query

Question

I am currently trying to create a report in Visual Studio 2005 for MSSQL 2005. I need to provide a multi-valued drop-down box that would allow a user to select one or more options. If a user selects one or more of these options, my SQL statement would need to add a AND column = 'something' to my SQL query.

I am wondering how I might accomplish this. Is this possible or do I need to create an ASP page with these options and then forward that to the report server? I am going in the wrong direction? Is there a better way to do this?

I wish I could just create a few check box controls but apparently this is not an option (read something about hacking it with text boxes and wingdings -- unacceptable).

Thanks in advance for you help.

Answer 1

(Assuming you are using a SQLServer data source,) edit your query to include AND column IN (@ParameterName) .

If your ParameterName parameter didn't already exist, it should now appear on the list of Report Parameters available from the Report menu. Select it in the Report Parameters dialog and check the Multi-value check box (if not already checked).

If you now try running the report in the preview window, you should find that ParameterName can have multiple values selected.

EDIT, following comments:

A couple of possibilities -

1. Using dynamic SQL: hardcode the available values for the multi-select parameters, so that their labels show user-friendly descriptions of the type of e-mail address to be included, and their values hold the field names to be selected. Set the query at run-time to be like "...and eMailID in (" & Join(Parameters!EMailField.Value, ", ") & ")". Dynamic SQL is generally deprecated due to the risk of SQL injection attacks, but since you will be hard-coding the only available values, those should not be an issue.

2. Using IN clauses within IN clauses: hardcode the available values for the multi-select parameters, so that their labels show user-friendly descriptions of the type of e-mail address to be included, and their values hold meaningful codes (such as O, G and S). Then amend the query to be like:

and eMailID in

   ( (case when 'O' in (@EMailField) then OwnersEmailField else NULL end),
     (case when 'G' in (@EMailField) then GeneralEmailField else NULL end),
     (case when 'S' in (@EMailField) then ServerEmailField else NULL end)
   )