CODEWITHSUNDEEP
sql-server-2008reporting-services
vpbot
vpbot

Reputation: 2487

Handling Multiple Values in SS Reports

I have a question, I have an SSRS report with multiple value field, I was just wandering how can I parse it from the SQL stored procedure that I have created eg.

Multiple Values -> Egg,Banana,Apple,Candies,Honey

SQL: 

SELECT Qty, Amount FROM tblFoodList WHERE ListOfFood IN (?Multiple Values?)

How am I able to put the multiple values inside the IN CLAUSE, likewise is this the correct way, or is there any other way..

Should I pass it as a string like?

@varTemp = 'Egg','Banana','Apple','Candies','Honey' 

SELECT Qty, Amount FROM tblFoodList WHERE ListOfFood IN (@varTemp)

Btw, I'm using SQL 2008 R2, and the string manipulation approach for SQL

Upvotes: 0

Views: 88

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

Since you're using a stored procedure, SSRS will always pass a CSV list; there is no option for a table-valued parameter or anything like that.

So it will be a string that looks like 'Egg,Banana,Apple,Candies,Honey'.

Next step is to turn this string into a some sort of table variable and use that in the IN clause; there are any numbers of techniques you can use here.

Erland Sommarskog has maintained a great list of techniques.

This SO question has various alternatives, too.

If you embed the query directly in the report, you can reference the variable like IN (@varTemp); it's essentially dynamic SQL so this will build the appropriate query. An alternative to consider if you don't feel like putting too much logic in an SP.

Upvotes: 1

Related Questions