Display Records without passing parameter values or by passing Single or multiple values in parameter in SSRS

Question

Within my SSRS report I am passing three parameters The Third parameter (which is a Search parameter) allows null value as well as multiple values. Reports giving results by passing null values or by passing one value to the third parameter, but throwing error when we pass more than 1 value in the third parameter.

I am using query below in the dataset to handle this case

IF (@Parameter3 = '')
BEGIN
    SELECT *
    FROM   [MyTableName] WITH(NOLOCK)
    WHERE  col1 IN (@Parameter1)
           AND col2 IN (@Parameter2)
END
ELSE
BEGIN
    SELECT *
    FROM   [MyTableName] WITH(NOLOCK)
    WHERE  col1 IN (@Parameter1)
           AND col2 IN (@Parameter2)
           AND col3 IN (@Parameter3) 
END

Here Parameter 3 is used to pass null as well as multiple Values.

However I get results when I pass single value in @ Parameter3 and also when I pass ` NULL` Value but I am getting Report Error when I Pass Multiple values in @Parameter 3 — user2728106

pedram pedram · Accepted Answer · 2016-06-27T06:27:14

@Parameter3 should have some separation - Either using comma or any special character.

For example: If you have passed multiple values thru @Parameter3 and that separates data with comma , then your query should looks like below.

IF (@Parameter3 = '')
BEGIN
    SELECT *
    FROM   [MyTableName] WITH(NOLOCK)
    WHERE  col1 IN (@Parameter1)
           AND col2 IN (@Parameter2)
END
ELSE
BEGIN
    SELECT *
    FROM   [MyTableName] WITH(NOLOCK)
    WHERE  col1 IN (@Parameter1)
           AND col2 IN (@Parameter2)
           AND col3 IN ISNULL(@Parameter3,'NULL')
END

Display Records without passing parameter values or by passing Single or multiple values in parameter in SSRS

1 Answers