1

I am re-creating a report that have been built with some obscure reporting technology and the report have some parameters (dropdown box filters). All the dropdown allow only one value to be selected at a time, but also include the 'All' selection at the top of the list. So you can see the report without filter(All) or filter it on a single value selection. Is this possible in SSRS?

PS: I would do it with multi value and the same query in default value to select all, but since I have 6 parameters, the main query ends up with 6 "WHERE...IN..." clause and takes more then 15 minutes to run.

Any suggestion would be greatly appreciated.

Elound.

Elound
  • 49
  • 4
  • 14
  • 1
    It's not only possible, it's easy. What part of it do you have a question about? – Tab Alleman Nov 03 '14 at 19:31
  • 1
    On a single-value parameter (a dropdown that allow you to select one value at a time) how to add a value that select all – Elound Nov 03 '14 at 19:41

1 Answers1

1

Add a value to each drop-down with "All" for the label and value.

Then in your SQL you handle each parameter with the value of the parameter that was passed, OR if the value was "All" then don't filter by that parameter.

Something like:

WHERE (SomeColumn = @SomeParameter OR @SomeParameter = 'All')
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • 1
    Thanks, that is what I was thinking and it is exactly what I was looking for! – Elound Nov 03 '14 at 20:09
  • 1
    It could be good for future consultation of this question to explain how to add the 'All' option to SomeParameter if the parameter is from a query (UNION ALL SELECT 'All'), if you feel like editing your answer. – Elound Nov 03 '14 at 20:13