ssrs user typing in multiple values in a parameter or leaving it blank

Question

I am creating a report in SSRS and have a parameter for the end users to search for an account number. Within the report the parameter is set to null by default to show all accounts. When the parameter is not null the user should be able to type in a single account or multiple accounts separated by commas. My problem is when I try to type in multiple accounts I get this error:

"An expression of non-boolean type specified in a context where a condition is expected, near ','."

Here is my sql query:

SELECT * FROM test_db WHERE (AccountNumber IN (@Account)) OR (@Account IS NULL)

The code on the parameter for @Account is

=IIF(IsNothing(Parameters!Account.Value),Parameters!Account.Value,Split(Parameters!Account.Value,","))

SELECT * FROM test_db WHERE (AccountNumber IN (@Account)) OR (AccountNumber IS NULL) You might mean this. AccountNumber instead of @Account in OR — Snowlockk
@Account is a data type Text in SSRS and a varchar(30) in the database. I tried adjusting the sql query to your suggestion but then it doesn't display all the accounts when the parameter is left null — Cowboyz209
You need to use Dynamic SQL. Something like .. exec ('select * from test_db where (AccountNumber in (' + @Account + ')) OR AccountNumber IS NULL' ) — Tak
Is it comma deliminated? if so you need to split it to do the In statement. — Snowlockk

Alan Schofield Alan Schofield · Accepted Answer · 2017-03-31T19:59:24

You don't need to do anything with it. If you make the @Account parameter multi-value, you can simply type each entry followed by [Enter], no commas, just your required value, one on each row of the parameter drop down.

Then your original query will work, SSRS will automatically convert the multi-value list into a comma separated list and inject it into your SQL. If this doesn't make sense, let me know and I'll put a complete answer with some images. You may need to change you query to

SELECT * 
    FROM test_db 
    WHERE AccountNumber IN (@Account) 
        OR ISNULL(@Account, '' ) = ''

This will take care of blanks too. So blanks/Null should return everything

ssrs user typing in multiple values in a parameter or leaving it blank

1 Answers