Multi value parameter help needed

Hi
I have a problem my database table has my parameter value
UserId integer
I need to set up my report to accept more than one id at a time (ex.
2, 44, 5). I went through the report parameters in reporting services
and set the parameter to accept multi value. When I tried to run the
report I was allowed to put in multi values with no problem, but it is
returning an error saying that it cannot convert data type nvarchar to
int. Any ideas?
thanks for the help
twoI'm guessing that your multi-value parameter has a lable and a value.
You probably set the parameter up wrong in Report Parameters. You
have a select with (label, value) pairs (apple, 2), (orange, 44),
(peach, 5). When you select apple, orange and peach you are expecting
2, 44, and 5, but you have probably set it up to pass in apple,
orange, peach. Which leads to the conversion problem you are seeing.
Are the options coming from a datasource or did you just type them
into the report parameters section?|||On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> I'm guessing that your multi-value parameter has a lable and a value.
> You probably set the parameter up wrong in Report Parameters. You
> have a select with (label, value) pairs (apple, 2), (orange, 44),
> (peach, 5). When you select apple, orange and peach you are expecting
> 2, 44, and 5, but you have probably set it up to pass in apple,
> orange, peach. Which leads to the conversion problem you are seeing.
> Are the options coming from a datasource or did you just type them
> into the report parameters section?
I am using a stored procedure that requires only one paramter which is
the id (int). The options for the parameters are not coming from a
datasource, Its pulling from my datasource that id (int) is a
parameter. So there is no label since the available values section is
set to non-queried.|||On Oct 5, 10:10 am, Twobridge <Twobri...@.gmail.com> wrote:
> On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> > I'm guessing that your multi-value parameter has a lable and a value.
> > You probably set the parameter up wrong in Report Parameters. You
> > have a select with (label, value) pairs (apple, 2), (orange, 44),
> > (peach, 5). When you select apple, orange and peach you are expecting
> > 2, 44, and 5, but you have probably set it up to pass in apple,
> > orange, peach. Which leads to the conversion problem you are seeing.
> > Are the options coming from a datasource or did you just type them
> > into the report parameters section?
> I am using a stored procedure that requires only one paramter which is
> the id (int). The options for the parameters are not coming from a
> datasource, Its pulling from my datasource that id (int) is a
> parameter. So there is no label since the available values section is
> set to non-queried.
I am thinking my problem isnt the parameter setting but my stored
procedure on the sqlserver. It is set up to accept a integer as it
parameter not an array of int. And to be honest i have no idea how to
set a stored procedure up to accept an array of integers.|||I have never used multi-select as a textbox that I put values in. I always
put in the values to select from, either a manual list I put in for the
parameter or a query. To make sure multi-select is working for you put in a
list of parameter values. This will make sure the issue is not your textbox
input.
The second issue I see is that you are trying to pass an multi-select list
to a stored procedure of type int. What the multi-select does is allow you
to multi-select from an option list. If you put in 1,12,34 into a listbox
this is by definition a string, it is not integers.
OK, now on to your stored procedure. There is absolutely no way to pass
multiple integers into a singe integer parameter. You are trying to get RS
to do something impossible. A way to look at this, if you cannot do it from
SQL Management Studio where you invoke the SP yourself, then there is no way
RS can do it either.
Also, for another problem what you want cannot be done without special code
in a stored procedure. If you have a query this would work:
select * from sometable where somefield in (@.id)
In this case the data type of id can be int.
Now with your stored procedure with a parameter of type int called id, it
only accepts a single integer.
Below is a post of mine from previous that describes the issue with
multi-parameters and stored procedures. What the problem is and how to get
around it:
>>>>>
What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Twobridge" <Twobridge@.gmail.com> wrote in message
news:1191597037.153027.72250@.w3g2000hsg.googlegroups.com...
> On Oct 5, 9:30 am, jer...@.gmail.com wrote:
>> I'm guessing that your multi-value parameter has a lable and a value.
>> You probably set the parameter up wrong in Report Parameters. You
>> have a select with (label, value) pairs (apple, 2), (orange, 44),
>> (peach, 5). When you select apple, orange and peach you are expecting
>> 2, 44, and 5, but you have probably set it up to pass in apple,
>> orange, peach. Which leads to the conversion problem you are seeing.
>> Are the options coming from a datasource or did you just type them
>> into the report parameters section?
> I am using a stored procedure that requires only one paramter which is
> the id (int). The options for the parameters are not coming from a
> datasource, Its pulling from my datasource that id (int) is a
> parameter. So there is no label since the available values section is
> set to non-queried.
>|||On Oct 5, 10:41 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never used multi-select as a textbox that I put values in. I always
> put in the values to select from, either a manual list I put in for the
> parameter or a query. To make sure multi-select is working for you put in a
> list of parameter values. This will make sure the issue is not your textbox
> input.
> The second issue I see is that you are trying to pass an multi-select list
> to a stored procedure of type int. What the multi-select does is allow you
> to multi-select from an option list. If you put in 1,12,34 into a listbox
> this is by definition a string, it is not integers.
> OK, now on to your stored procedure. There is absolutely no way to pass
> multiple integers into a singe integer parameter. You are trying to get RS
> to do something impossible. A way to look at this, if you cannot do it from
> SQL Management Studio where you invoke the SP yourself, then there is no way
> RS can do it either.
> Also, for another problem what you want cannot be done without special code
> in a stored procedure. If you have a query this would work:
> select * from sometable where somefield in (@.id)
> In this case the data type of id can be int.
> Now with your stored procedure with a parameter of type int called id, it
> only accepts a single integer.
> Below is a post of mine from previous that describes the issue with
> multi-parameters and stored procedures. What the problem is and how to get
> around it:
> What doesn't work has nothing really to do with RS but has to do with Stored
> Procedures in SQL Server. You cannot do the following in a stored procedure.
> Let's say you have a Parameter called @.MyParams
> Now you can map that parameter to a multi-value parameter but if in your
> stored procedure you try to do this:
> select * from sometable where somefield in (@.MyParams)
> It won't work. Try it. Create a stored procedure and try to pass a
> multi-value parameter to the stored procedure. It won't work.
> What you can do is to have a string parameter that is passed as a multivalue
> parameter and then change the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Twobridge" <Twobri...@.gmail.com> wrote in message
> news:1191597037.153027.72250@.w3g2000hsg.googlegroups.com...
>
> > On Oct 5, 9:30 am, jer...@.gmail.com wrote:
> >> I'm guessing that your multi-value parameter has a lable and a value.
> >> You probably set the parameter up wrong in Report Parameters. You
> >> have a select with (label, value) pairs (apple, 2), (orange, 44),
> >> (peach, 5). When you select apple, orange and peach you are expecting
> >> 2, 44, and 5, but you have probably set it up to pass in apple,
> >> orange, peach. Which leads to the conversion problem you are seeing.
> >> Are the options coming from a datasource or did you just type them
> >> into the report parameters section?
> > I am using a stored procedure that requires only one paramter which is
> > the id (int). The options for the parameters are not coming from a
> > datasource, Its pulling from my datasource that id (int) is a
> > parameter. So there is no label since the available values section is
> > set to non-queried.- Hide quoted text -
> - Show quoted text -
Thanks everyone who helped me out. Bruce you were correct that it was
more to do with my stored procedure that RS. I found a really good
example of a function that basically took in my string of numbers and
was able to break up the string into my needed id's . Here is a link
if anyone needs it.
http://www.sommarskog.se/arrays-in-sql-2005.html