SSRS: How to add All option to SSRS dropdown filter?

Question

I'm using SQL Server Reporting Services for SQL 2008 R2. My reports are filtered by two drop-down menus that are populated from a table, one of them displays a build number. I'd like to give users the option to choose "All" and so return data for all build numbers and not just one. How do I add this option to my drop-down filter and make it work correctly? Thanks very much for any help provided. J.

Solution

I'm assuming you don't want to use a multi-value parameter here, you only want users to run against all builds or just one, not a selection of builds. Otherwise you'd just use a standard multi-value parameter.

One way to do this is to return an extra row for all builds in your parameter dataset, e.g. something like:

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

I'm returning two columns here so we can pass a NULL value parameter but still have a user-friendly description to display in the report.

Set this up as your parameter dataset. In the report code you can then use the parameter to do something like:

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

Which will return all builds when @build is null and a specified build if @build is not null.

Comments

[bearaman]

Thanks Ian. After thinking about it, the multi-value parameter suits my needs best and is built-in so I'll go with that. Thanks for your help with this. J.

[Matt]

That'll only work for string fields, which might be fine depending on your usage.