Default to SELECT ALL in SQL Server Reporting Services

I have a report with 10 multi-value dropdowns using IN(@Value) for each value. Each value is query driven with the muli selector. When we run the run the report each time you have to select something in all 10 dropdowns. I want to make 9 of them Select All as a default.
allenkentAsked:
Who is Participating?
 
Nico BontenbalCommented:
In the 'Report Parameter Properties' windows there is a 'Default Values' section. Select the option 'Get values from a query' there and choose the same Dataset and 'Value field' as you did in the 'Available Values' section.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Each value is query driven with the muli selector.
My usual method is to put together a UNION statement, where the first choice is the 'all', and make the first column the one used in the main query:

SELECT -1 as id, ' < ALL > ' as name, 1 as sort_order
UNION
SELECT id, name, 2 as sort_order
FROM SomeTable
ORDER BY sort_order, id

... then in the main query handle it like this ...

SELECT blah, blah, blah
FROM Whatever
WHERE (@parameter = -1 OR some_id = @parameter)

Hope this helps.
Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.