Specify Values in SSRS parameter

  • Hi!

    I have a SSRS report , parameter @a uses 'specify values' (integer 1 and 2) as available values.

    What I need now is dropdown 'All' or 'Total' which would give me total result from 1 and 2 which are integer values.

    Currently I am able to get for individual values only.

    How can I include result from 1 and 2 if clicked 'All' dropdown?

    Thanks in advance.

  • If you are calling stored procedure then in the procedure I'd do it like this:

    IF @parameter IS NULL -- assuming that you pass NULL when ALL is selected

    BEGIN;

    SELECT * FROM TABLE;

    END;

    ELSE

    BEGIN;

    SELECT * FROM TABLE WHERE COLUMN = @parameter;

    END;

    If you are not calling a stored procedure but have embedded sql I'd do something like this in the dataset:

    ="SELECT * FROM Table " & IIf(! ISNOTHING(Parameters!ReportParameter1.Value), "WHERE Column = " & Parameters!ReportParameter1.Value, "" )" (

  • What about setting the parameter to multivalue? Then you should have a "Select all"-Entry in your drop down.

  • MicVog (7/22/2014)


    What about setting the parameter to multivalue? Then you should have a "Select all"-Entry in your drop down.

    That still can require some special handling as the "ALL" really just selects each item and passes them as a delimited list, it doesn't pass a specific value and it wasn't clear that 1 and 2 were the only possible values for the column in the database but may be the key values that are necessary to separate searches on.

  • I've noticed in the case of a multi value parameter, the Select ALL option shows when the code is embedded in the reports. That is, a stored procedure is not being called.

    ----------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply