4

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.

bearaman
  • 1,071
  • 6
  • 23
  • 43
  • Take a look into this thread http://stackoverflow.com/questions/12917261/optional-multi-valued-parameters-in-ssrs/12931993#12931993 – Aftab Ansari Feb 18 '13 at 06:56
  • possible duplicate of [How do I allow a drop-down list parameter in SSRS to have a default value of "-- All -- "?](http://stackoverflow.com/questions/14328712/how-do-i-allow-a-drop-down-list-parameter-in-ssrs-to-have-a-default-value-of) – Tim Abell May 22 '15 at 16:56

2 Answers2

10

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.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • 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. – bearaman Feb 18 '13 at 14:32
0

Would it be correct to simply change the where clause in the stored procedure to 

Where [field] LIKE @variable

Then in SSRS under the Available Values have the "ALL" parameter value be % (the percent symbol) ?

Is there an error in logic here. It seems to have the desired result

BGDev
  • 135
  • 1
  • 8