Select multiple value and/or ALL for SQL

Terry St Jean 81 Reputation points

I have a SSRS report where there is a dropdown where the user can select one or multiple product values to filter the report by.
In the prompt, I have to add "ALL" at the top of the list of products, which I can do.
My question is how to handle this 'ALL' value in SQL for the report data.
In the SQL, if they select multiple products, I can have WHERE products in ('the list from the prompt').
But if they select ALL, ALL isn't a product. Or if the select ALL and some products, it should return all products.
I can build the SQL as a text string and if the value ALL is in the prompt string, then exclude this part of the WHERE clause and then EXECUTE sp_executesql but is there a way of handling the 'ALL' value another way other than building the SQL manually?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,751 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,351 questions
0 comments No comments
{count} votes

Accepted answer
  1. AniyaTang-MSFT 5,471 Reputation points Microsoft Vendor

    Hi @Terry St Jean
    Are you trying to achieve this result? Add an "All" option in the parameter drop-down list, when "All" or "All" and other options are selected, all data will be returned.
    Then you can try this approach:

    1. Add or ('All' in (@Parameter)) after the Where clause in the main dataset query;
    2. Add the "All" option to the query dataset. After the parameters are set normally, the result will be as I have shown.
      If I misunderstood you please tell me more information.
      Best regards,
    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful