January 17, 2008 at 5:53 am
I've followed many blogs and forums now on how to pass the values from a multi-value parameter in reporting services to a stored procedure, and, although I think I understand the concept I can't seem to get it to work. I've created a split function to separate out the selected values in to a table that can be referenced in an 'IN' clause in the stored procedure as below:
CREATE PROCEDURE [dbo].[spStoredProc]
@client char(6)
AS
BEGIN
SET NOCOUNT ON;
select d.* from table1 d
where d.clnt_no in (SELECT Item FROM dbo.Split(@client, ','))
END
and placed
=Join(Parameters!client.Value,", " )
in the value field for the parameter drop-down in the Dataset Parameters tab for the stored proc (command type: Stored procedure) in my RS report but it only ever seems to report back the very first selection in the parameter drop down, which suggests it isn't firing all the values to the in statement in the stored procedure.
Can someone suggest where I'm going wrong here please? I don't think it's the function but may be wrong.
January 17, 2008 at 3:04 pm
Take a look at your split function and ensure that it is matching what you are passing in.
Per your "Join" statement you are putting a blank after the comma. E.g., the resulting string is "A, B, C" and not "A,B,C".
So this could be affecting your "IN" list matching.
Test out (unit test) your stored procedure independent of SSRS to ensure that if you pass the right delimited values you get the correct results.
The input parameter should also be coded as VARCHAR and not CHAR as CHAR will blank pad. I also think that 6 characters is a bit small to handle a comma delimited string of values. What are the lengths of the values and how many can be passed?
I suggest that the parameter should be defined as VARCHAR(8000) to handle an [almost] open-ended list of values.
January 17, 2008 at 4:53 pm
I'd agree with everything above. Also, run the profiler to get the exact string that RS is passing in, so you can test with something real.
January 18, 2008 at 4:02 am
Thanks both for the suggestions. Having tested with values in the split function I had it produced the values I was expecting. The key was the varchar v char data length. I'd originally written the stored procedure to expect one value as the field was 6 characters in length. When it was converted to take multi valued parameters I forgot to change that. The split function worked fine. Sometimes you need someone else to look at your code before you see the obvious! At least I know how to do this now until Microsoft make this a little more user-friendly from within ssrs.
January 21, 2008 at 9:41 am
Update (JohnG): It appears that the posting (excerpt in the quoted text below) that I reference in my reply has been removed by the original poster after reading my response.
where (charindex(d.clnt_no,@client, 1)>0)
I disagree.
For starters, because you are using a function on the search column (clnt_no) in the WHERE clause, you will be negating any index usage on this column. Try this with a large table and see the query plan.
Additionally, depending upon the list of values that are passed in the parameter in conjunction with the values that are in the table, you could get a lot of "false positives" due to the character matching behavior of CHARINDEX. E.g., given clint_no values of 100 and 1000, a WHERE clause of: CHARINDEX(CAST(d.clnt_no AS VARCHAR), '1000', 1) > 0 will incorrectly return both 100 and 1000!
March 17, 2008 at 11:14 am
Is it possible to control a parameter type as being single vs. multi-valued based on the value selected in the previous parameter? For example in parameter one the values are: City, State. In Parameter two the values are the respective list of cities or states.
If city is selected in parameter one then the user should only be able to select one city from parameter two. If State is selected in parameter one I want parameter two to be a mult-select.
If this is possible, could it be done via the XML code or would it be possible through any Visual Basic coding?
March 17, 2008 at 11:21 am
I would create two different secondary parameters, populated by the appropriate queries, and then show/hide the appropriate parameter based upon the selection of the first parameter.
March 17, 2008 at 11:49 am
Is this the same idea of a Cascading Parameter? If not how do you hide or make available a parameter. Under report properties if I check 'Hidden', then parameter three will not be visible if I select 'State' from parameter one. If the parameter becomes unavailable then I will not pass a value for that respective parameter to the SQL stored procedure, correct?
September 3, 2010 at 7:35 am
Hi Experts,
I have a similar problem.. I got a table with values, and I need to update second table through stored procedure, because in procedure are done some calculation... How to perform that? Any example would be very useful for me.
Thanking all you in advanced and best regards
April 3, 2012 at 9:07 am
I found myself in the same situation and after reviewing many posts couldn't find a simple solution. Then by light bulb turned on.
This is what I did
1) I set on of the parameters of my SP for my dataset as:
="SELECT '"+JOIN(Parameters!MultiValue1.Value,"' UNION SELECT '") + "'"
This works for 1,2,...N values selected and it creates a string like this:
SELECT 'Value1' UNION
SELECT 'Value2'
.
.
.
2) In my SP i placed this code
CREATE TABLE #Tbl_MultiValues(Value_ VARCHAR(100))
DECLARE @SQLStr VARCHAR(8000)
SET @SQLStr = 'INSERT INTO #Tbl_MultiValues' + @Value_
EXECUTE (@SQLStr);
Then in my WHERE clause
WHERE TableValue IN(SELECT * FROM #Tbl_MultiValues)
I hope you find this trick usefull...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply