Catch-all clause in combo box filters for SQL Reporting Services

Here's the scenario. Suppose you want to create a report for a data set in Microsoft SQL Server Reporting Services, using report parameters to filter it. Let's call the parameter @Parameter1, and suppose that @Parameter1 can take values from a table called ValueTable. You wish to allow the user to pick an option for this parameter, but also you wish to allow them to pick a 'wildcard' option, which means "I don't care what the value of this parameter is."

This is a bit tricky. To accomplish this, ValueTable must have an identity primary key. Given this constraint, create a new data set and populate it as follows:

SELECT -1, '(All)' UNION
SELECT IdCol, DescriptionCol FROM ValueTable

You should set up @Parameter1 so that it queries the above dataset.

Next, in your main report dataset, if @Parameter1 is used to constrain Column1 from Table1, you should set things up like so:

SELECT Column2, Column3, ... FROM Table1
WHERE Column1 >=
(CASE @Parameter 
   WHEN -1 THEN 0 
   ELSE @Parameter1
END)
AND Column1 <=
(CASE @Column1
   WHEN -1 THEN (SELECT MAX(IdCol) FROM ValueTable)
   ELSE @Parameter1
END)

This particular setup works because ANDing both inequalities in the case when @Parameter1 <> -1 ensures that IdCol will be equal to @Parameter1. Otherwise, the inequalites merely request that @Parameter1 lies within the range of IdCol, which is always true so it acts as a wildcard.

HTH,
George