Default to SELECT ALL in SQL Server Reporting Services

Posted on 2012-08-21
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
    LVL 65

    Assisted Solution

    by:Jim Horn
    >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

    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 65

    Expert Comment

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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    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 …
    A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. (htt…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now