CODEWITHSUNDEEP
c#sqlasp.netreporting-servicessplit
Athanasios Karagiannis
Athanasios Karagiannis

Reputation: 308

SQL Server Report Builder 3 using SQL IN operator with parameter not working

I am attempting to filter a dataset in SSRS using the IN operator on a single column. When I add more than one value it fails.

For simplicity this is my SQL statement excluding other filters to focus on the new problem.

SELECT * FROM vActivityDetailed WHERE Category IN (@Category)

@Category is affected by an override expression under Dataset properties.

=Split(Parameters!Category.Value, ",")

Instead of using a multivalue parameter, I treat this as a single value and hope that by doing a split, the SSRS should execute like this.

C# - ASP.NET Code

string categoryString = "Expenses, Misc, Accounts-Receivable";
paramPayType.Name = "Category";
paramPayType.Values.Add((categoryString!= "" ? categoryString : null));

SSRS Final SQL Execution (Expected)

SELECT * FROM vActivityDetailed WHERE Category IN ('Expenses', 'Misc', 'Accounts-Receivable')

Since I've tested this directly on the SQL using SQL Server Management Studio, I know the above SQL should work since it returns records, but when applied in SSRS it fails with An error has occurred during report processing. (rsProcessingAborted)

Upvotes: 2

Views: 761

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

The parameter value in SSRS is an array of strings when multiple values are selected. The Split function takes a single string as its argument. You can't use Split with an array. SSRS with SQL Server handles the splitting of the values for you so you should be able to just pass the parameter straight through.

If you need to actually split the values for some reason, I would recommend using a custom SQL function. There are lots of examples if you run a search for that.

Upvotes: 2

Related Questions