Default to SELECT ALL in SQL Server Reporting Services

Posted on 2012-08-21
Medium Priority
Last Modified: 2012-08-22
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.
Question by:allenkent
  • 2
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 38319044
>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
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.
LVL 22

Accepted Solution

Nico Bontenbal earned 1000 total points
ID: 38319190
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.
LVL 66

Expert Comment

by:Jim Horn
ID: 38322409
Thanks for the split.  Good luck with your project.  -Jim

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question