April 11, 2008 at 4:30 am
where in(@Code) it works
but where in('"+parameters!Code.Value+"') I am writing but it doesnt work why??
April 11, 2008 at 6:29 am
select ... from ...
where ... in(@Code)
it works
but
="
select .. from ...
where ... in('"+parameters!Code.Value+"')
I am writing but it doesnt work
why??
April 11, 2008 at 7:32 am
Hi,
First of all I will point you to this article which discusses how mult-value parameters are handled by SSRS.
The main point from the article is that multi-value parameters are handled differently to single value parameters in SSRS before being passed to the SQL Server.
Now that is out of the way we can move on to the interesting bit.
Basically the reason you cannot reference the mulit-valued parameter variable directly within your query is because it is not a scalar. The variable is infact stored as System.Obect[] which is essentially an indexed array of parameters and not as a comma delimited string as you might expect.
In order to reference a value / label pair from the variable you would need to access it via the relevant index. For example to select the first value stored in the variable you could use Parameters!MultiList.Value(0).ToString() or Parameters!MultiList.Label(0).ToString() for the label.
As you can see from the above example in order to use the IN condition would require you to loop through the array of values and construct the sql statement dynamically. Since SSRS does this for you anyway all you need to do is reference the variable as @Variable and all the annoying stuff is done for you.
I hope this helps you understand what is going on.
This is all based on my experience and understanding of SSRS so if anyone else out there can help fill in the gaps it would be greatly appreciated. Remember, learning is best achieved through sharing.
Christopher New
(:cool: MVP in training :))
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply