Friday, March 30, 2012

Multi value parameters

Hi All

I'm trying to initialize (checkmark) only the first selection of a Multi Value list, (built from an SQL), I can initialize all or nothing.

I tried to use (0) next to the value field name in the default value, but that causes an error (Field is not valid because of the(0)).

Thank you

Trentino

Please try function =First()|||

Hi Lev

sorry for the slow response, I did try to use function First(), I get this error

"Aggregate function cannot be used in report parameter expression"

Thanks

Trentino

|||

I was wrong, this does not work.

There is a way however to do it with help of another parameter

Create internal single-value parameter, say Parameter1, and set default value "from query". Value of this parameter will be taken from 1st record of the data set

Then create multi-value parameter, set default value "non queried" and value expression =Parameters!Parameter1.Value

|||

Thank you, I did not try it yet.

I hardcoded the value of the first record for testing purpose and it did work, so I'm sure that once the new parameter is filled with the correct value it will work.

Thank you again.

Multi value parameters

Hi All

I'm trying to initialize (checkmark) only the first selection of a Multi Value list, (built from an SQL), I can initialize all or nothing.

I tried to use (0) next to the value field name in the default value, but that causes an error (Field is not valid because of the(0)).

Thank you

Trentino

Please try function =First()|||

Hi Lev

sorry for the slow response, I did try to use function First(), I get this error

"Aggregate function cannot be used in report parameter expression"

Thanks

Trentino

|||

I was wrong, this does not work.

There is a way however to do it with help of another parameter

Create internal single-value parameter, say Parameter1, and set default value "from query". Value of this parameter will be taken from 1st record of the data set

Then create multi-value parameter, set default value "non queried" and value expression =Parameters!Parameter1.Value

|||

Thank you, I did not try it yet.

I hardcoded the value of the first record for testing purpose and it did work, so I'm sure that once the new parameter is filled with the correct value it will work.

Thank you again.

Multi Value Parameters

Hi all, I am a beginner of SQL Server 2005.

How do you display multi-values parameters with coma in the SQL Server 2005 reporting?.

I believe we have to create a fucntion to do this, but how do you create a function in SQL Server 2005? we only can create / alter table.

I have an example from a book but it doesnt help me at all. it just give me an example of the code but when I tried, it did not work coz its a function like in VBA.

I want in the report look like this in the Layout:

SubCityID: =Code.ParameterList(Parameter!City.Value)
SubStore: =Code.ParameterList(Parameter!Store.Label)

Then in the report will looks like this.

SubCityID: 1, 5, 35
SubStore: Alabama, Hybird, Zap

The code for ParameterList that I ger looks like this:

Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = ""

For each sParamItem in Parameter
If sParamItem is Nothing Then Exit For
sParamVal &= sParamItem & ", "
Next

Return sParamVal.SubString(0, sParamVal.Length - 2)

End Function

If there is a better way please help me.

Cheers

Quote:

Originally Posted by monadel

Hi all, I am a beginner of SQL Server 2005.

How do you display multi-values parameters with coma in the SQL Server 2005 reporting?.

I believe we have to create a fucntion to do this, but how do you create a function in SQL Server 2005? we only can create / alter table.

I have an example from a book but it doesnt help me at all. it just give me an example of the code but when I tried, it did not work coz its a function like in VBA.

I want in the report look like this in the Layout:

SubCityID: =Code.ParameterList(Parameter!City.Value)
SubStore: =Code.ParameterList(Parameter!Store.Label)

Then in the report will looks like this.

SubCityID: 1, 5, 35
SubStore: Alabama, Hybird, Zap

The code for ParameterList that I ger looks like this:

Function ParameterList(ByVal Parameter As Object) As String
Dim sParamItem As Object
Dim sParamVal As String = ""

For each sParamItem in Parameter
If sParamItem is Nothing Then Exit For
sParamVal &= sParamItem & ", "
Next

Return sParamVal.SubString(0, sParamVal.Length - 2)

End Function

If there is a better way please help me.

Cheers


Retrieve the whole SubCityId from the database using the following query and use it in your back end code...

declare @.sParamItem varchar(1000)

SET @.sParamItem = ''

SELECT @.sParamItem = @.sParamItem + convert(varchar(3),SubCityId) + ', '
FROM Table_Name WHERE My_Condition

select @.sParamItem

Multi value parameters

Can anyone give me the syntax to pass mutliple parameters into a stored
procedure? I know that you have to use IN, but I cannot get it to work. Thank
you in advance.Create your Dataset, select the Proc and execute it in Reporting Services
Designer. It will create your parameters for you, then you can go into the
Report > Parameters section and adjust the settings from there. What are you
using IN for? Can you post some code that you are using?
"KimB" wrote:
> Can anyone give me the syntax to pass mutliple parameters into a stored
> procedure? I know that you have to use IN, but I cannot get it to work. Thank
> you in advance.|||As a side note, RS 2005 supports multi-value parameters natively, so no need
of an expression-based query.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"KimB" <KimB@.discussions.microsoft.com> wrote in message
news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> Can anyone give me the syntax to pass mutliple parameters into a stored
> procedure? I know that you have to use IN, but I cannot get it to work.
> Thank
> you in advance.|||I'm trying to pass a multi-value to a stored procedure as well but it isn't
working. The documentation says that using a SP with multi-value isn't
supported. Huh? Most of my reports use SPs.
Can this be done? Is there a workaround?
This was the #1 feature I was waiting for in RS2005.
Thanks in advance!
"Teo Lachev [MVP]" wrote:
> As a side note, RS 2005 supports multi-value parameters natively, so no need
> of an expression-based query.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > procedure? I know that you have to use IN, but I cannot get it to work.
> > Thank
> > you in advance.
>
>|||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
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> I'm trying to pass a multi-value to a stored procedure as well but it
> isn't
> working. The documentation says that using a SP with multi-value isn't
> supported. Huh? Most of my reports use SPs.
> Can this be done? Is there a workaround?
> This was the #1 feature I was waiting for in RS2005.
> Thanks in advance!
>
> "Teo Lachev [MVP]" wrote:
>> As a side note, RS 2005 supports multi-value parameters natively, so no
>> need
>> of an expression-based query.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "KimB" <KimB@.discussions.microsoft.com> wrote in message
>> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
>> > Can anyone give me the syntax to pass mutliple parameters into a stored
>> > procedure? I know that you have to use IN, but I cannot get it to work.
>> > Thank
>> > you in advance.
>>|||I understand now-- thanks-- and this worked great for me.
It's too bad the documentation doesn't provide this simple information given
that the multi-value feature is bound to be a highly-desired feature.
Thanks again!
"Bruce L-C [MVP]" wrote:
> 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
> "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > I'm trying to pass a multi-value to a stored procedure as well but it
> > isn't
> > working. The documentation says that using a SP with multi-value isn't
> > supported. Huh? Most of my reports use SPs.
> >
> > Can this be done? Is there a workaround?
> >
> > This was the #1 feature I was waiting for in RS2005.
> >
> > Thanks in advance!
> >
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> As a side note, RS 2005 supports multi-value parameters natively, so no
> >> need
> >> of an expression-based query.
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> >> > procedure? I know that you have to use IN, but I cannot get it to work.
> >> > Thank
> >> > you in advance.
> >>
> >>
> >>
>
>|||I've been using the charlist_to_table function to return my parameters in a
table and it works well but I can't get it to return more than 800 rows--
even if the string sent contains more than 800 entries.
I checked and RS is sending ALL of the selected entries (868 in this case)
but the function is only returning 800 rows.
I used a similiar function from elsewhere on the net and it also returned
only 800 rows.
Is anyone aware of a limitation here-- I don't see any limitation in the
function?
Thanks!
"Michael Creamer" wrote:
> I understand now-- thanks-- and this worked great for me.
> It's too bad the documentation doesn't provide this simple information given
> that the multi-value feature is bound to be a highly-desired feature.
> Thanks again!
>
> "Bruce L-C [MVP]" wrote:
> > 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
> >
> > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > > I'm trying to pass a multi-value to a stored procedure as well but it
> > > isn't
> > > working. The documentation says that using a SP with multi-value isn't
> > > supported. Huh? Most of my reports use SPs.
> > >
> > > Can this be done? Is there a workaround?
> > >
> > > This was the #1 feature I was waiting for in RS2005.
> > >
> > > Thanks in advance!
> > >
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > >> As a side note, RS 2005 supports multi-value parameters natively, so no
> > >> need
> > >> of an expression-based query.
> > >>
> > >> --
> > >> HTH,
> > >> ---
> > >> Teo Lachev, MVP, MCSD, MCT
> > >> "Microsoft Reporting Services in Action"
> > >> "Applied Microsoft Analysis Services 2005"
> > >> Home page and blog: http://www.prologika.com/
> > >> ---
> > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > >> > procedure? I know that you have to use IN, but I cannot get it to work.
> > >> > Thank
> > >> > you in advance.
> > >>
> > >>
> > >>
> >
> >
> >|||It is working now.
I had been using a similar function, UtilSplit, but I've now switched to the
charlist_to_table function and all the rows are being returned correctly.
"Michael Creamer" wrote:
> I've been using the charlist_to_table function to return my parameters in a
> table and it works well but I can't get it to return more than 800 rows--
> even if the string sent contains more than 800 entries.
> I checked and RS is sending ALL of the selected entries (868 in this case)
> but the function is only returning 800 rows.
> I used a similiar function from elsewhere on the net and it also returned
> only 800 rows.
> Is anyone aware of a limitation here-- I don't see any limitation in the
> function?
> Thanks!
> "Michael Creamer" wrote:
> > I understand now-- thanks-- and this worked great for me.
> >
> > It's too bad the documentation doesn't provide this simple information given
> > that the multi-value feature is bound to be a highly-desired feature.
> >
> > Thanks again!
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > 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
> > >
> > > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> > > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > > > I'm trying to pass a multi-value to a stored procedure as well but it
> > > > isn't
> > > > working. The documentation says that using a SP with multi-value isn't
> > > > supported. Huh? Most of my reports use SPs.
> > > >
> > > > Can this be done? Is there a workaround?
> > > >
> > > > This was the #1 feature I was waiting for in RS2005.
> > > >
> > > > Thanks in advance!
> > > >
> > > >
> > > > "Teo Lachev [MVP]" wrote:
> > > >
> > > >> As a side note, RS 2005 supports multi-value parameters natively, so no
> > > >> need
> > > >> of an expression-based query.
> > > >>
> > > >> --
> > > >> HTH,
> > > >> ---
> > > >> Teo Lachev, MVP, MCSD, MCT
> > > >> "Microsoft Reporting Services in Action"
> > > >> "Applied Microsoft Analysis Services 2005"
> > > >> Home page and blog: http://www.prologika.com/
> > > >> ---
> > > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> > > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > > >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > > >> > procedure? I know that you have to use IN, but I cannot get it to work.
> > > >> > Thank
> > > >> > you in advance.
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||I had passed on your question to the author of the charlist_to_table
function. I'll tell him nevermind.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:540D9E63-DD7F-478B-89E1-D36B6BF1FC4B@.microsoft.com...
> It is working now.
> I had been using a similar function, UtilSplit, but I've now switched to
> the
> charlist_to_table function and all the rows are being returned correctly.
> "Michael Creamer" wrote:
>> I've been using the charlist_to_table function to return my parameters in
>> a
>> table and it works well but I can't get it to return more than 800 rows--
>> even if the string sent contains more than 800 entries.
>> I checked and RS is sending ALL of the selected entries (868 in this
>> case)
>> but the function is only returning 800 rows.
>> I used a similiar function from elsewhere on the net and it also returned
>> only 800 rows.
>> Is anyone aware of a limitation here-- I don't see any limitation in the
>> function?
>> Thanks!
>> "Michael Creamer" wrote:
>> > I understand now-- thanks-- and this worked great for me.
>> >
>> > It's too bad the documentation doesn't provide this simple information
>> > given
>> > that the multi-value feature is bound to be a highly-desired feature.
>> >
>> > Thanks again!
>> >
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > 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
>> > >
>> > > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
>> > > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
>> > > > I'm trying to pass a multi-value to a stored procedure as well but
>> > > > it
>> > > > isn't
>> > > > working. The documentation says that using a SP with multi-value
>> > > > isn't
>> > > > supported. Huh? Most of my reports use SPs.
>> > > >
>> > > > Can this be done? Is there a workaround?
>> > > >
>> > > > This was the #1 feature I was waiting for in RS2005.
>> > > >
>> > > > Thanks in advance!
>> > > >
>> > > >
>> > > > "Teo Lachev [MVP]" wrote:
>> > > >
>> > > >> As a side note, RS 2005 supports multi-value parameters natively,
>> > > >> so no
>> > > >> need
>> > > >> of an expression-based query.
>> > > >>
>> > > >> --
>> > > >> HTH,
>> > > >> ---
>> > > >> Teo Lachev, MVP, MCSD, MCT
>> > > >> "Microsoft Reporting Services in Action"
>> > > >> "Applied Microsoft Analysis Services 2005"
>> > > >> Home page and blog: http://www.prologika.com/
>> > > >> ---
>> > > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
>> > > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
>> > > >> > Can anyone give me the syntax to pass mutliple parameters into a
>> > > >> > stored
>> > > >> > procedure? I know that you have to use IN, but I cannot get it
>> > > >> > to work.
>> > > >> > Thank
>> > > >> > you in advance.
>> > > >>
>> > > >>
>> > > >>
>> > >
>> > >
>> > >

Multi Value Parameters

Hi,
I am new to RS and the very first project that I had to implement was to
enhance a report which was taking a single param into multi value(not
multiple parameters). I bought 3 books and all were useless. Thought about
for a week to come with the design. I designed the application and it works
absolutely marvellous..
I will be posting the details by COB today so that everybody can see how it
works and also would like to know the feedback..Hi,
I am very interested in Multi Value Parameters. Did you post anywhere how
you managed to create these ?
Thanks
--
Best Regards
Maria Sartzetaki
"Suresh" wrote:
> Hi,
> I am new to RS and the very first project that I had to implement was to
> enhance a report which was taking a single param into multi value(not
> multiple parameters). I bought 3 books and all were useless. Thought about
> for a week to come with the design. I designed the application and it works
> absolutely marvellous..
> I will be posting the details by COB today so that everybody can see how it
> works and also would like to know the feedback..
>sql

Multi Value Parameters

I have an issue using a Muiti Value Parameter. Upto a point it works
quite well.
The parameter is based on a field within the report & so when I preview
I get a list of values, and can select all, or some records. Thats the
bit that works.
However when I run the report it returns data for for 1 value only. I
have made sure that the filter on my report uses 'in' instead of '='.
One thing I would rater do thatn use the report filter is pass the
parameter directly to my SQL statement, does anyone know how this can
be done?
Thanks
PaddySo you're using a filter on the dataset (retrieve all rows first). This is
not very efficient, especially if you're dealing with tables with millions
of rows...
To do it on the data source side:
If your data source is SQL Server 2005, you can simply write your TSQL as
"...and myTable.myField IN (@.myParameter)"
If your data source is SQL Server 2000 or something else you could do either
of the following:
stored proc, pass the parameter to the stored proc as
=join(Parameters!myParameter.Value, ",")
Then write some code that splits your values out by "," and writes them to
an in-memory join table. Typically in the past, we've used a UDF to return
a table as well.
For a "straight query" (IOW TSQL, PLSQL, etc), as long as the data source
supports the IN clause, you could write dynamic SQL in the form:
="select ..... where myTable.myValue IN (" &
join(Parameters!myParameter.Value, ",") & ")"
If you go the dynamic sql path, write the query out first normally so you
don't have to fill in the field list, then change it to dynamic.
Clear as mud?
-Tim
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:1152180850.976447.110760@.75g2000cwc.googlegroups.com...
>I have an issue using a Muiti Value Parameter. Upto a point it works
> quite well.
> The parameter is based on a field within the report & so when I preview
> I get a list of values, and can select all, or some records. Thats the
> bit that works.
> However when I run the report it returns data for for 1 value only. I
> have made sure that the filter on my report uses 'in' instead of '='.
> One thing I would rater do thatn use the report filter is pass the
> parameter directly to my SQL statement, does anyone know how this can
> be done?
> Thanks
> Paddy
>

Multi- value parameter!

Hi every body!
I have two reports ,in first i have one multi value parameter .in
first report i select more than one value ,then in second report i
want to have these values becuse i have another multi value
parameter ,when i join these reports via navigation
in parameters (in first report) i put the values of multi value
parameter in fisrt report for value of multi value parameter in second
report,but in this case fore example i have it:
in first reprt-> navigation->parameters->
ParameterName ParameterVaue
EmployeeID =Parameters!EmployeeID.Value(0)
but in second report i need all of my selections in first report not
just first value
Can anybody help me?On Nov 4, 4:35 am, SHIMAR...@.gmail.com wrote:
> Hi every body!
> I have two reports ,in first i have one multi value parameter .in
> first report i select more than one value ,then in second report i
> want to have these values becuse i have another multi value
> parameter ,when i join these reports via navigation
> in parameters (in first report) i put the values of multi value
> parameter in fisrt report for value of multi value parameter in second
> report,but in this case fore example i have it:
> in first reprt-> navigation->parameters->
> ParameterName ParameterVaue
> EmployeeID =Parameters!EmployeeID.Value(0)
> but in second report i need all of my selections in first report not
> just first value
> Can anybody help me?
If I'm understanding you correctly, you should be able to use an
expression similar to the following to obtain the multi-select
parameter's values from the main report.
=Join(Parameters!EmployeeID.Value,",")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant