Multi Value Parameters

I just created a report with 2 parameters and it works fine. If I change one of them to MV it still works but if I pick 2 values I get a message about a problem with the "," - Incorrect syntas near ','

Is this a known problem or is it something I'm doing. Is a problem is there a fix for it.

YOu habe to change your query after changing the parameter type, as your query probably look like

WHERE SomeColumn = @.MyParameter

which will evaluate in a MV-Parameter to

WHERE SomeColumn = 'A','B'

This is not valid SQL. Therefore you to write your query like the one below.

WHERE SomeColumn IN (@.MyParameter)

That should work for you and your value list.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Works perfect - thank you.

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.

sql

Multi Value Parameters

Hi,

Can anyone tell me how to pass a data for a multivalue parameter through the URL.

Thanks In Advance

Regards

Raja Annamalai S

To set a parameter named "Name1" to value1 and value 2, use this format:

http://localhost/reportserver?/MyReport&Name1=value1&Name1=value2

|||Thank you very much.

Multi Value Parameters

I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.

Code Snippet

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

This should help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

See Lisa's posts.

You can't just use IN (@.Region). SQL doesn't automatically understand that. Developing this can be extemely frustrating, but don't despair. We're here to help if you get stuck.

|||

I also faced same problem.In reporting service, even though you select multi values for parameter and send it to stored procedure,it wil go as a single Value.

U got it.In stored procedure,if u included where clause with IN,it wil consider it as a single value only(as 'a,b,c,d').

So, u need to split this (comma seperated).You have to include split function into ur stored procedure.Search in google, u wil get it.

I same i used in my report and its working fine.Its very easy.

|||

There's a reat article on what you need to do here:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Working_with_Multivalued_Parameters

including some copy and paste code.

Enjoy!

-Eric

Multi Value Parameters

I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.

Code Snippet

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

This should help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

See Lisa's posts.

You can't just use IN (@.Region). SQL doesn't automatically understand that. Developing this can be extemely frustrating, but don't despair. We're here to help if you get stuck.

|||

I also faced same problem.In reporting service, even though you select multi values for parameter and send it to stored procedure,it wil go as a single Value.

U got it.In stored procedure,if u included where clause with IN,it wil consider it as a single value only(as 'a,b,c,d').

So, u need to split this (comma seperated).You have to include split function into ur stored procedure.Search in google, u wil get it.

I same i used in my report and its working fine.Its very easy.

|||

There's a reat article on what you need to do here:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Working_with_Multivalued_Parameters

including some copy and paste code.

Enjoy!

-Eric

Multi Value Parameters

Hi,

Thanks for the reply...I think I should have been a little more clearer.

I can specify the multi paramaters, but I want to be able to select certain values from that parameter like in the pic.

E.G. I have a dropdown with City's in, but I dont want to select one, which is what I can only do at the moment, I want to be able to put a tick in the tickbox next to it to select more than one?

Does this make sense?
Yes with multi-value parameter you can do that!!!
I have multi-value paramters and I can select as many values as I want.
I'm using RS 2005 april ctp and in june ctp also works.
What are you using?|||Dear Friend,

I have problems using multi values parameters in my reports, maybe you can help me.

In a simple store procedure like this:

...
@.parametro int
as
select *
from tb_Fact_Vehicles
where id_Dim_Location in (@.parametro)
...

whe I select one value in the preview screen the repors runs fine, but if I select more than one parameter the results is nothing, I mean empty: no records. Do you have any idea what's happening?

|||Ah...I'm using RS 2000 SP2. Is there anyway I can do this in RS2000? or do I need to move to RS2005?

Is RS2005 stable enough for a live environment?

Cheers|||RS2005 is not for production purpose. Wait until november 7th.
I think it's possible in RS2000, but I've never tested RS2000.
I'm sorry.

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.

sql

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 Passing in OLEDB or ODBC

Hi Robert
We are integrating Sql server 2005 with sybase through ODBC.We are using
named parameters in the query...query which will fetch data from sybase
database.Now named parameters are those parameters which we specify with
'@.'.i.e. @.emp_id.
As we are conneting sybase from sql server 2005 through ODBC...but ODBC
doesnt support named parameter...so we are using unnamed parameters (?)
that is for e.g.
Select emp_id,emp_name from t_employee where emp_id = ?
but this is single value unnamed paramater...do u have idea how to pass
multivalue unnamed parameter which i can use with "In" clause..
Select emp_id,emp_name from t_employee where emp_id in ()
if i use ? along with "In" clause...it throws an error saying "Multivalue
Parameter is not supported in Data Extension"
Based on your last thread , we try to get something regarding creation of Custom Data Extension, but unfortunately we are not able to find much more on this particular topic.
We are able to find some readymain Custom Data Extension code from following link
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
but unfortunately we are not able to find more from that link also.
Can u plz help us regarding this, as we are passing through very crucial problem.
We will really very glad, if you can help us to solve this problem.
Thanks in advance :).

Take your sql and replace the ? with a valid emp_id and then run the query:

Select emp_id,emp_name from t_employee where emp_id = 2056

Goto report parameters and delete all of the query parameters the sql was using. Then create a report parameter named EmpID. Set in to multivalue. You then can change your sql to the following:

="Select emp_id,emp_name from t_employee where emp_id in (" & Parameters!EmpID.Value & ")"

You have to follow the process as described. Also, if EmpId is a string then you need to surround your values with single quotes. ie. When you select your drop down to choose the EmpID's to run for you should see" '20034' and '23456' etc. This will then pass a comma delimited string value to the SQL.

multi value parameter

I've been reading the forums regarding this but don't seem to understand
exactly how this is supposed to work. I have parameters list build from
query. Report Parameter is set to multi value. The SP that the report uses
has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
report and choose 'select all' I get no results though if I select just one,
it works fine. Selecting more than one also returns no result.
What can I possibly be doing wrong?SQL 2005, SP2
"brian" wrote:
> I've been reading the forums regarding this but don't seem to understand
> exactly how this is supposed to work. I have parameters list build from
> query. Report Parameter is set to multi value. The SP that the report uses
> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
> report and choose 'select all' I get no results though if I select just one,
> it works fine. Selecting more than one also returns no result.
> What can I possibly be doing wrong?|||You answered yourself but unfortunately you answered incorrectly. Here is my
stock answer for this:
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
One other option is to create dynamic SQL in your stored procedure as well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"brian" <brian@.discussions.microsoft.com> wrote in message
news:F270F0D4-6551-444A-9711-E5B0FE938063@.microsoft.com...
> SQL 2005, SP2
> "brian" wrote:
>> I've been reading the forums regarding this but don't seem to understand
>> exactly how this is supposed to work. I have parameters list build from
>> query. Report Parameter is set to multi value. The SP that the report
>> uses
>> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
>> report and choose 'select all' I get no results though if I select just
>> one,
>> it works fine. Selecting more than one also returns no result.
>> What can I possibly be doing wrong?|||Way over my head; I have more reading to do. Thanks Bruce.
"Bruce L-C [MVP]" wrote:
> You answered yourself but unfortunately you answered incorrectly. Here is my
> stock answer for this:
> 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
> One other option is to create dynamic SQL in your stored procedure as well.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "brian" <brian@.discussions.microsoft.com> wrote in message
> news:F270F0D4-6551-444A-9711-E5B0FE938063@.microsoft.com...
> > SQL 2005, SP2
> >
> > "brian" wrote:
> >
> >> I've been reading the forums regarding this but don't seem to understand
> >> exactly how this is supposed to work. I have parameters list build from
> >> query. Report Parameter is set to multi value. The SP that the report
> >> uses
> >> has line: PARAMETER IN (@.Parameter) in the WHERE clause. When I run the
> >> report and choose 'select all' I get no results though if I select just
> >> one,
> >> it works fine. Selecting more than one also returns no result.
> >>
> >> What can I possibly be doing wrong?
>
>

Multi Value Dependent report parameters help

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=343618&SiteID=17

In continuition with above thread i would to like to ask another question.

Can we make Multivalue parameters to play for cascading parameters. I am trying with different combinations of using query parameters and Report parameters.

Can any body help me?

Regards

Raj Deep.A

Hi,

I've not tried it myself, but I think it should work.

In the second (dependent) parameter, you should use the IN in the WHERE clause like:

WHERE Region IN (@.Country)

Could you give a try?

Jordi Rambla
MVP SQL Server (Reporting Services)
Certia - rambla informàtica (http://www.certia.net)
Solid Quality Learning (http://www.solidqualitylearning.com)

|||I've done this and using the in clause does work.|||

Hello heather,

I will be really glad if you can send the RDL to me. actually i was for the first parameter its working ,for the second parameter the query runs and the result values were dropped down.I can't see even check boxes and i am unable to select the appropriate value(No action) and remains senseless just like label on canvas.

If in that example ,if it was working, i am really glad and you are great

pls send it to ralapati@.smart-bridge.co.in

Thank you,

Raj Deep.A

|||

Pls reply ,how did we do that?I am waiting for your valuable reply

Thank you,

Raj Deep.A

|||

I worked on this for some time around.

It's working fine now

Thank you,

Raj Deep.A

Multi Select Parameters?

In SQL Reporting Services, Is there anyway to create a parameter in
which a user can be prompted to choose multiple values from a list?
Ultimately, the multi-selected values would be passed to the Dataset
query as an "IN" qualification.
Example:
SELECT first_name, last_name
FROM employees
LEFT OUTER JOIN department ON
employees.dept_id = department.dept_id
WHERE dept_name IN ('Accounting', 'Sales', Support')
Crystal Reports has this functionality but I can not figure out how to
do this in MSRS.
If you know how to do this and would like to help a guy out, could you
include step by step answer or a link to a step by step tutorial?
Thanks!Here are a couple of options:
Option #1: Use a query expression as follows: ="select top 10 name, type
from sysobjects where type in (" & Parameters!Report_Parameter_0.Value &
")".
Option #2: Check
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=0a3800c4-4180-419c-a117-bfa21b2de099.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"The Whistler" <sharris@.SLeasynews.com> wrote in message
news:ltnrg01f5qf251e89c5vsrmcdn7fu01k4i@.4ax.com...
> In SQL Reporting Services, Is there anyway to create a parameter in
> which a user can be prompted to choose multiple values from a list?
> Ultimately, the multi-selected values would be passed to the Dataset
> query as an "IN" qualification.
> Example:
> SELECT first_name, last_name
> FROM employees
> LEFT OUTER JOIN department ON
> employees.dept_id = department.dept_id
> WHERE dept_name IN ('Accounting', 'Sales', Support')
> Crystal Reports has this functionality but I can not figure out how to
> do this in MSRS.
> If you know how to do this and would like to help a guy out, could you
> include step by step answer or a link to a step by step tutorial?
> Thanks!sql

Multi Select Parameter in RS2005

I'm using the possibility to define some parameters of my reports having the
property multi-select. On all my reports I always give information on the
first page on the parameters used to generate the output. For parameters that
do not have the property multi-select this works by using expressions
= Parameters!id_status.Value and = Parameters!id_status.Label
where id_status is a parameter
For a multi-select parameter I want to do the same; how ?I guess the new release of RS 2005 does support multi value select. I was
reading FAQ section on Microsoft site, that it is possible to pass multi
value parameters into the report. The way it is implemented is like this.
In a dropdown which lists the values, check all the values and then view the
report. RS constructs the SQL behind the scene.
Hope this helps..
Suresh
"RDC" wrote:
> I'm using the possibility to define some parameters of my reports having the
> property multi-select. On all my reports I always give information on the
> first page on the parameters used to generate the output. For parameters that
> do not have the property multi-select this works by using expressions
> = Parameters!id_status.Value and = Parameters!id_status.Label
> where id_status is a parameter
> For a multi-select parameter I want to do the same; how ?|||I known it works; I want to display the selected values in my report by using
something like
= Parameters!parameter_name.Value and = Parameters!parameter.Label
"Suresh" wrote:
> I guess the new release of RS 2005 does support multi value select. I was
> reading FAQ section on Microsoft site, that it is possible to pass multi
> value parameters into the report. The way it is implemented is like this.
> In a dropdown which lists the values, check all the values and then view the
> report. RS constructs the SQL behind the scene.
> Hope this helps..
> Suresh
> "RDC" wrote:
> > I'm using the possibility to define some parameters of my reports having the
> > property multi-select. On all my reports I always give information on the
> > first page on the parameters used to generate the output. For parameters that
> > do not have the property multi-select this works by using expressions
> > = Parameters!id_status.Value and = Parameters!id_status.Label
> > where id_status is a parameter
> > For a multi-select parameter I want to do the same; how ?|||If you change a report parameter to be multi value, the .Value property will
return an object[] rather than an object. Hence you can no longer e.g. write
expressions like =Parameters!MVP1.Value.ToString().
To access individual values of a multi value parameter you can use
expressions like this:
=Parameters!MVP1.IsMultiValue
boolean flag - tells if a parameter is defined as multi value
=Parameters!MVP1.Count
returns the number of values in the array
=Parameters!MVP1.Value(0)
returns the first selected value
=Join(Parameters!MVP1.Value)
creates a space separated list of values
=Join(Parameters!MVP1.Value, ", ")
creates a comma separated list of values
=Split("a b c", " ")
to create a multi value object array from a string (this can be used
e.g. for drillthrough parameters, subreports, or query parameters)
See also MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctjoin.asp
* http://msdn.microsoft.com/library/en-us/vbenlr98/html/vafctsplit.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"RDC" <RDC@.discussions.microsoft.com> wrote in message
news:4CBB1232-BEC3-4D0F-BC55-F1FD732F3AB8@.microsoft.com...
>I known it works; I want to display the selected values in my report by
>using
> something like
> = Parameters!parameter_name.Value and = Parameters!parameter.Label
> "Suresh" wrote:
>> I guess the new release of RS 2005 does support multi value select. I was
>> reading FAQ section on Microsoft site, that it is possible to pass multi
>> value parameters into the report. The way it is implemented is like this.
>> In a dropdown which lists the values, check all the values and then view
>> the
>> report. RS constructs the SQL behind the scene.
>> Hope this helps..
>> Suresh
>> "RDC" wrote:
>> > I'm using the possibility to define some parameters of my reports
>> > having the
>> > property multi-select. On all my reports I always give information on
>> > the
>> > first page on the parameters used to generate the output. For
>> > parameters that
>> > do not have the property multi-select this works by using expressions
>> > = Parameters!id_status.Value and = Parameters!id_status.Label
>> > where id_status is a parameter
>> > For a multi-select parameter I want to do the same; how ?

Multi select parameter dropdown

Hi there,
Does anyone know if it is possible to make drop down parameters multiple select? Much like one can in a list box by holding down ctrl and clicking on the records, but for parameters.
Please any help will be great.
RegardsIn box?
How could I let parameter accept multi value in a textbox?
Just like i want two parameter values "2003, 2004" in one parameter textbox,
then select the product in 2003 and 2004?
My syntax is..
Select A,B,C From XYZ Where (A in (@.Aparameter) or @.Aparameter='')
How should I do?
Thanks!
Angi
"Myles" <Myles@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:5F9259CD-6036-4F16-B26E-C694085DD0CB@.microsoft.com...
> I believe you can select multiple rows in a drop down, but I don't believe
that Reporting Services (out of the box) will accept multiple values for a
parameter. There may be a way to use the WebService, however - there is a
ReportParameter.MultiValue Property available in there - it is read only,
however. Is it possible to have multi valued parameters using the web
service?
> "PLSH" wrote:
> > Hi there,
> >
> > Does anyone know if it is possible to make drop down parameters multiple
select? Much like one can in a list box by holding down ctrl and clicking on
the records, but for parameters.
> >
> > Please any help will be great.
> >
> > Regards

multi parameters problem in CTP June

In CTP June Reporting Service and try the multi-value,
use Adventure Work DB and the SQL as follow..
SELECT Name, CountryRegionCode
FROM Person.CountryRegion
WHERE (CountryRegionCode = @.code)
When I checked the multi-value then browse,
I keyin the multi value AF, AL and the textbox show as AF, AL
click " View Report " then response the error message:
An error has occurred during report processing.
Query execution failed for data set 'Adventure'.
Incorrect syntax near ','.
What's happen?
How could I use multi-value in reporting services?
The multi-value dose work on AS Cube Report, but dosen't work on relational
DB?
Thanks for any advice!
AngiYou have to use the IN keyword in the query:
SELECT Name, CountryRegionCode
FROM Person.CountryRegion
WHERE CountryRegionCode (IN @.code)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Angi" <enchiw@.msn.com> wrote in message
news:%23ndxNNduFHA.3660@.tk2msftngp13.phx.gbl...
> In CTP June Reporting Service and try the multi-value,
> use Adventure Work DB and the SQL as follow..
> SELECT Name, CountryRegionCode
> FROM Person.CountryRegion
> WHERE (CountryRegionCode = @.code)
> When I checked the multi-value then browse,
> I keyin the multi value AF, AL and the textbox show as AF, AL
> click " View Report " then response the error message:
> An error has occurred during report processing.
> Query execution failed for data set 'Adventure'.
> Incorrect syntax near ','.
> What's happen?
> How could I use multi-value in reporting services?
> The multi-value dose work on AS Cube Report, but dosen't work on
> relational DB?
> Thanks for any advice!
> Angi
>