SSRS: how to add all parameters to the SSRS drop-down filter?

Question

I am using SQL Server Reporting Services for SQL 2008 R2. My reports are filtered by two drop-down menus that are populated from the table, one of them displays the assembly number. I would like to give users the opportunity to select "All" and thus return data for all build numbers, and not just for one. How to add this parameter to my drop-down filter and make it work correctly? Thanks so much for the help provided. J.

Answer 1

I assume that you do not want to use a multi-valued parameter here, you want users to run against only all assemblies, or only one, not the assembly selection. Otherwise, you simply use the standard parameter with multiple values .

One way to do this is to return an additional row for all assemblies in the parameter data set, for example. sort of:

select buildId as null, build = 'All' union all select buildId = build, build from builds 

I am returning two columns here so that we can pass a parameter to NULL values, but still have a description that is convenient for describing in the report.

Define this as a parameter data set. In the report code, you can use a parameter to do something like:

 select * from builds where (@build is null or @build = build) 

Which will return all assemblies if @build is null and the specified assembly if @build not null.

Answer 2

It would be correct to simply change the where clause in the stored procedure to

 Where [field] LIKE @variable 

Then, in SSRS, in the "Available Values" section, will the "ALL" parameter value be% (percent symbol)?

Is there a mistake in the logic here. It seems to have the desired result.