CODEWITHSUNDEEP
reporting-services
common763
common763

Reputation: 1

Adding 'Select All' option to a Single-Select Parameter in SSRS

Not an advanced coder. I need to add a 'Select All' option that will return all the results in the parameter...Normally I do this easily utilizing multi-select but am being asked to do this with a single select. So the end-user can choose either ALL or one of the options in the parameter. I was told to add an all option but not not visualizing. Can anyone give some advice? Thanks.

Upvotes: 0

Views: 2450

Answers (1)

molleyc
molleyc

Reputation: 349

Depending on how you're getting your parameter list, you could do this a couple different ways:

  1. If you're using a Stored Procedure (SQL Query to a db) to get your list of items in your parameter, you can add this to the top of your query, and it will add the new option to the list (make sure you add the same columns to this part of the query as you have in your table's query):

    SELECT
         0 AS Id
         , 'ALL' AS Name
    UNION
    SELECT DISTINCT
         Id, Name
    FROM YourTable

  2. If you're hardcoding your parameter items, you could add the extra option there:

Report Parameter Window with "ALL" = 0 option

Then, when you run the report, assuming you're using a Stored Procedure to get the results from the db, you can do the following:

  1. Pass in the parameter at the top of the Stored Proc:

    @Param1 INT

  2. Use it as a filter at the end of your SELECT query in the WHERE clause:

    WHERE ((@Param1 IS NULL OR @Param1 = 0) OR (@Param1 = YourTable.YourColumn))

Upvotes: 2

Related Questions