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!

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

Multi value parameter use in "IN" expression.

Hi
While writing my latest report i have been trying to use the result of a
multi value parameter as the value for a "IN" expression in my SQL query,
this does not seem to work, the multi value parameter shows that it has
values in it but the "IN" expression wont use them to filter the SQL query.
I have since found that the way i am using the Parameter in the SQL is
correct but i am now unsure of how the parameter should be set up.
Can anyone help?
Steve D'IN' should have multiple values seperated with a comma. I hope you know
that. So you need to use =join() to build the multi value parameters into
something like this (1,2,3,4) then pass this value into the query it should
work.
Amarnath
"Steve Dearman" wrote:
> Hi
> While writing my latest report i have been trying to use the result of a
> multi value parameter as the value for a "IN" expression in my SQL query,
> this does not seem to work, the multi value parameter shows that it has
> values in it but the "IN" expression wont use them to filter the SQL query.
> I have since found that the way i am using the Parameter in the SQL is
> correct but i am now unsure of how the parameter should be set up.
> Can anyone help?
> Steve D
>
>|||When I use a multi-value parameter I just do this:
select somefield from sometable where anotherfield in (@.Param)
Where @.Param maps to a multi-value parameter. There is no need for anything
fancy unless passing the parameter to a stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:56A2A663-692A-460A-8D64-4FD1F1B0B80C@.microsoft.com...
> 'IN' should have multiple values seperated with a comma. I hope you know
> that. So you need to use =join() to build the multi value parameters into
> something like this (1,2,3,4) then pass this value into the query it
> should
> work.
> Amarnath
>
> "Steve Dearman" wrote:
>> Hi
>> While writing my latest report i have been trying to use the result of a
>> multi value parameter as the value for a "IN" expression in my SQL query,
>> this does not seem to work, the multi value parameter shows that it has
>> values in it but the "IN" expression wont use them to filter the SQL
>> query.
>> I have since found that the way i am using the Parameter in the SQL is
>> correct but i am now unsure of how the parameter should be set up.
>> Can anyone help?
>> Steve D
>>

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 not working

I have installed MS sql server 2005 developer edition beta2.
In report designer,i have given a parameter to a multi value and when see in
preview i am able to see the multi valued parameter.
but when i run it and see it in reportserver,even though i select multi
values,while displaying the results its taking only the last value selected.
please help me in this regard.I think that with Beta 2 there were some problems with multi-value. It is
working in the release version. If you have MSDN you can download it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sirisha" <sirisha@.discussions.microsoft.com> wrote in message
news:D7D10346-8D31-4810-A287-AD6D1D41030B@.microsoft.com...
>I have installed MS sql server 2005 developer edition beta2.
> In report designer,i have given a parameter to a multi value and when see
> in
> preview i am able to see the multi valued parameter.
> but when i run it and see it in reportserver,even though i select multi
> values,while displaying the results its taking only the last value
> selected.
> please help me in this regard.
>sql

Multi value parameter issue?

How do I make a mvp optional? I don't want to always have to select a
value for it.
Thanks in advanceAs far as I can see, you just can't make it optional.
An alternative to your users could be to (select all). That shouldn't be
that much of a problem, as I assume your report stored procedure already
accepts multiple values.
"gte401e" wrote:
> How do I make a mvp optional? I don't want to always have to select a
> value for it.
> Thanks in advance
>

Multi value parameter in db2 query

hi,

i'm trying to perform a query against a db2 database like this:

SELECT ... FROM ... WHERE (field IN (?))

Then i let reporting services pass the parameter to the report. When i try to preview the report, i get the following error:

An error occurred during local report processing,
An error has occured during report processing,
Cannot add multi value query parameter '?' for data set ... because it is not supported by the data extension

But when i type the query like this

SELECT ... FROM ... WHERE (field IN ('value1','value2'))

it executes flawlessly.

I am using the IBM ole db driver for db2 if that matters

Can anyone help me?

IBM's Ole DB driver only supports parameterized queries with single valued parameters. See e.g. this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=81223&SiteID=1

SSRS 2005 contains specific functionality to perform query rewrite for multi-valued query parameters in the cases of SQL Server, Oracle 9 or later, and SSAS / XML/A-based data sources.

For all other cases you would need a custom data extension implementation to provide the query rewrite functionality.

-- Robert

Multi value parameter help needed

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

Multi Value Parameter and Filtering

Hi All
I am trying to filter a table on my report based on a multi-value parameter,
I have tried several methods to no avail. When I use the following I do not
get an error but I do not get data showing on the report.
Expression is set to =Fields!Account.Value
Operator is set to In
Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
Can anyone help this newbie style question
RegardsI think you should be able to leave out the Join function.
Set the value as: =Parameters!Select_Individual_Account.Value
I do that in one of my reports and it works fine. Just make sure there is
not a (0) at the end of the paramerter.
"Are friends electric?" wrote:
> Hi All
> I am trying to filter a table on my report based on a multi-value parameter,
> I have tried several methods to no avail. When I use the following I do not
> get an error but I do not get data showing on the report.
> Expression is set to =Fields!Account.Value
> Operator is set to In
> Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
> Can anyone help this newbie style question
> Regards
>
>|||Thanks for helping Matt
I now get the following error
The filter expression for the table cannot be performed,
cannot compare data of type system.string and system.object
any ideas
Thanks
Steve
"Matt M" wrote:
> I think you should be able to leave out the Join function.
> Set the value as: =Parameters!Select_Individual_Account.Value
> I do that in one of my reports and it works fine. Just make sure there is
> not a (0) at the end of the paramerter.
> "Are friends electric?" wrote:
> > Hi All
> >
> > I am trying to filter a table on my report based on a multi-value parameter,
> >
> > I have tried several methods to no avail. When I use the following I do not
> > get an error but I do not get data showing on the report.
> >
> > Expression is set to =Fields!Account.Value
> > Operator is set to In
> > Value is set to =join(Parameters!Select_Individual_Account.Value, ",")
> >
> > Can anyone help this newbie style question
> >
> > Regards
> >
> >
> >
> >

Multi value Parameter

Hi,

In my report i have a multi valued parameter, when i view my report in the Web application with Report Viewer, The multi valued parameter is displaying in light color.Can we able to change this.

Thanks in advance

Mahima,

You can go to your Reporting Service\ReportManager\Style directory, this is were your "CSS" reporting service stylesheets are located. I believe the file you looking for is RSWebParts.css

Ham

|||

Hi,

Thanks for the reply.Can we able to change the width and Height of the multi valued parameter.Now it is of fixed size and displaying the horizantal and vertical scroll bars.

Thanks

multi value parameter

hi,

i read from this forum that to pass array to report is using multi value parameter . my problem now is

1. can i pass multi dimension array.

2. how do i show the multi value parameter in a table . for example i have a multi value parameter that may contain 10 or 20 array . how do i dynamic it show in table.

rgds,

charles

One way to do this is to use separate parameters for separate dimensions of the array. Would that work for you?

Another possibility might be to put the multiple dimensions of each row of the array into a delimited string and parse out the values for display (use the Split function in your expressions).

As far as how you should them in a table... you can probably kludge this too...

But if you want to show them in a table, why not just create a dataset from the array instead of trying to pass a param? ?

Maybe I'm missing your problem...

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 parameter

In our project we are using reporting service web service calls to get report
paramaeters and report data.
We are passing an array of parameter values to webservice method
GetReportData() as an input parameter to render the output bytes.This
parameter values array consists of each controls's selected value.
Now in case of multivalue parameters, how to pass these multivalues to
webservice method GetReportData()?
Suppose for the control if we select the multiple values, how to send them
to array along with the other control's values to parametervalues collection.
please give suggestions on these.
Thanx
sriramI got the answer for this multivalue parameter
We have to send the multivalue parmaeters with same parameter name but with
different selected values in to parameter values array as below:
Here Foo is a multivalue parameter
<ParameterValues>
<ParameterValue>
<Name>Foo</Name>
<Value>1</Value>
</ParameterValue>
<ParameterValue>
<Name>Foo</Name>
<Value>2</Value>
</ParameterValue>
<ParameterValue>
<Name>Bar</Name>
<Value>NorthWest</Value>
</ParameterValue>
</ParameterValues>
"SRIRAM" wrote:
> In our project we are using reporting service web service calls to get report
> paramaeters and report data.
> We are passing an array of parameter values to webservice method
> GetReportData() as an input parameter to render the output bytes.This
> parameter values array consists of each controls's selected value.
> Now in case of multivalue parameters, how to pass these multivalues to
> webservice method GetReportData()?
> Suppose for the control if we select the multiple values, how to send them
> to array along with the other control's values to parametervalues collection.
> please give suggestions on these.
> Thanx
> sriramsql

Multi Value Integer Parameter

I am trying to create a report with a multi value integer parameter. I have tried

="SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (" + Parameters!StatusCode.Value + ")"

I get an error stating

An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'ProjectRequestStatus'.

Error during processing of the CommandText expression of dataset 'ProjectRequestStatus'.

Has anybody worked with integers?

Thanks,

Fred

Why not just :

SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (@.StatusCode)

That should work fine. Just set up you parameter as an integer, and multi value.

BobP

|||

If Bobp's solution doesn't work, you can try putting the Join function around your parameter value. It might not be passing the parameters correctly. Something like this:

Code Snippet

="SELECT * FROM vProjectRequestStatus WHERE ProjectRequestStatusCode IN (" + Join(Parameters!StatusCode.Value, ",") + ")"

|||

When I try that, it errors out when I enter more than one value separated by commas when running the dataset. It works if I only enter one value.

The error is: Conversion failed when converting the nvarchar value '2, 3' to data type int.

When I preview the report I receive no errors but no data is displayed except in the page header and the first group header. (There is a 2nd and 3rd group) It does not matter whether I enter one or more values in the parameter. There is data in the Document Map which allows you to drill down to group 3 data.

Any ideas why there is no data?

Fred

|||

I just tried this solution by putting the join function in the expression. Like the solution above, I get the same results in the Preview. No data except in Group Header 1 and the Document Map allows me to drill down to Group 3.

I checked the Visibility flag in each cell and row and the whole table. All Hidden properties are False.

Fred

|||

Can you post the Parameter RDL snippet?

BobP

|||I found my problem. One of the groups did have the Visibility Hidden property set to True.

Multi value in Report Parameter using MSVS Report Designer.

Hi All,

I have a question, How to user the Multi value in Visual Studio Report Designer.

My dataset contains the following

Select empno,empname,basicpay,deptcd from employee where deptcd in (substring(@.gdept,1,4))

Here variable gdept is the report parameter. I am passing the value.
it works fine with single value.

( the source of gdept is a another dataset to list the deparment code+name,

Example

DEP1 - COMPUTER

DEP2 - ADMIN

DEP3 - FINANCE

)

when I preview, i have a combo box to select the department.

If I choose one deparment, it works fine.

If I choose more than one department or select all I have error message

"substring function requires 3 aruguments."

what is wrong is the query

Please advise.

Cheers,

Saleem

Thats nor possible within a single query. By choosing more than one Value it will evaluate to SUBSTR('A','B',1,4) which is not valid, because the signature of SUBSTRING Only allows 3 parameters. You will have to do that with splitting the values. I once wrote a function for that which will make those values Accessible for you as a table and which can be joined easily afterwards.

See more details on: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320221&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

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 value "Select All"

Hi all
I have a multi-value drop down list as my report's parameter.
Once the report is executed with the selected parameter, I am
displaying the selected parameter also as a part of the report's
results.
Is there a way I can display the word "All" in the report results when
the user selects "Select All" of the report parameter?
At present it shows a long list of concatenated values of the drop down
list.
Thanks heapsI might be misunderstanding you, but you can display the parameter
label that is selected: Parameter.label instead of parameter.value|||I am sorry if my question was not clear.
I tried Parameter.Label, but gives an error if the parameter is a Multi
Value type.
So, one has to use Join(Parameter.Label,",").
This displays a list of all the paramters that were selected.
What I want is, if the user selects "Select All" instead of showing the
list of parameters, i should be able to display the word "All".
Does this make sense?
Thanks

Multi threads

I have an app that is critical to our business. It handles and syncronises several SQL Servers, checks integrety etc. I need to make the app so it can run a few things at once. Does anyone have any experience with this? Currently we use Delphi and ADO. I have been fiddling with DMO to get more performance - I am not sure ADO is very quick for some I tasks I need to do.

I suppose my main question *really* is does ADO/DMO multi-thread and has anyone tried it. If not how do people do it?Yes, ADO emphatically multi-threads. I regularly run one app that for a few moments has 30+ threads running with no problems at all.

Then again, I'm well known for doing extremely perverse things with multi-threading (including a couple of multi-threaded batch files!).

-PatP|||That's interesting, is there anything you would recommend that I do/not do?

Also, do you have to use client cursors? I tend to find using more than one and I ger problems generally.|||Use connection pooling!!!
Use a separate connection object for every active recordset.
Close every connection as soon as you are done using it and free any memory it is/was using.
Keep your recordsets as small as practical (more threads, more network traffic, more contention).
When referencing database objects, do so strategically to avoid the app blocking itself

-PatPsql

Multi Threading in Stored Procedures?

hi,
I want to execute two user created stored procedures in a multithreaded manner in ms-sql server . Can some tell me how i can do that.
Thanks in advance.
Regards,
ManpreetOriginally posted by chugh_manpreet
hi,

I want to execute two users


Ya know....I've ALWAYS wanted to do that...

but then who would use the system?

Well, I guess if you limit to just 2....

To answer though...every exec od a sproc is threaded...it's not serial...

Are you talking about withIN the sproc itself?|||By its design the SQL engine is single-process multi-threaded. A series of asynchronous calls always result in multi-threaded processing on the server. I guess I have the same question as Brett, - are you talking about multi-threading within a stored procedure? And why do you need that?|||No chuckles?

Anyway...To thread in a sproc, create jobs and start'em...

They'll be aysyncronus and thread...

although I swear (and can't confirm) that using xp_cmdshell does...even though everywhere I read it's synchronous...(I couldn;t explain some blocking awhile back...still don't know why)|||I can! In pre-SP3 era when everybody and their mother was firing it while trying to be cute, and coming back with OSQL through it. There was a hell of a blocking going on. It is actually due to the nature of OSQL, rather than xp_cmdshell, but those who were using it didn't even bother to check into the possibility of it to occur, and we were stuck to debug, and what's most frustrating, - explain, how it happened. Now they are all squealing, because they can't do any more damage :)|||Yeah, but in a sproc if you do...

DELETE FROM myTable99

master xp_cmdshell 'bcp command...for myTable99'

Wouldn't you expect the DELETE to be completed before the bcp?

It actually launches 2 spids (of course) but spid 1 (the execution of the sproc and the delete) blocks spid 2, the execution of xp_cmdshell

huh?

Is this a fundamental thing I'm totally missing?|||hi,

thanks for your reply. Actually i have written two stored procedures which call a dll(non-sql) funtion. Now i want these two stored procedures simultaneously in a multi threaded manner.

I tried executing it like this

exec sp_Procedure1
exec sp_Procedure2

but the second stored procedures is not getting executed untill first gets executed. I want both the stored procedures execute simultaneously in a multi threaded manner.

Any inputs in this regard are welcome

Regards,
Manpreet

Originally posted by Brett Kaiser
No chuckles?

Anyway...To thread in a sproc, create jobs and start'em...

They'll be aysyncronus and thread...

although I swear (and can't confirm) that using xp_cmdshell does...even though everywhere I read it's synchronous...(I couldn;t explain some blocking awhile back...still don't know why)|||hi,

thanks for your reply. Actually i have written two stored procedures which call a dll(non-sql) funtion. Now i want these two stored procedures simultaneously in a multi threaded manner.

I tried executing it like this

exec sp_Procedure1
exec sp_Procedure2

but the second stored procedures is not getting executed untill first gets executed. I want both the stored procedures execute simultaneously in a multi threaded manner.

Any inputs in this regard are welcome

Regards,
Manpreet
Sorry for bumping an old post, but I am looking to do the same thing, pseudocode below for those that want to know why.

---------------
CREATE PROC COMPANY_ISP
@.company_id INT
AS

INSERT COMPANY(COMPANY_ID)
SELECT @.company_id

EXEC COMPANY_REBUILD_INDEX @.company_id

RETURN
---------------

The proc is actaully a little longer then the above but you get the idea, COMPANY_REBUILD_INDEX takes about 10 seconds to run and waiting for it to process is not critical to the web page calling this proc, but rather a hindrance since the application needs to wait for this process to finish.

I suppose the application could call this proc separately behind the scenes somehow, I can talk with the .Net developer to see what he thinks, but thought it would be nice to have a solution that would work in SQL. xp_cmdshell? I thought that was only for DOS commands, does not seem like it is the solution I am looking for.

Thanks,
-John|||Applicaton design issues should be handled by the application designers.

If I want an application to run two stored procedures simultaneously, then I expect the application to thread the executions. If the applicaton wants serial execution, then serialize them.

You should consider not only the near term results, but also the long term maintainability. Cute solutions usually compromise the latter for the former.

For your solution, it seems you are expecting an inherently single threaded application (browser) to handle asynchronous communications.

The reindex should probably be done via a job that detects the request (writes a flag somewhere), and then you can write a control that polls a progress table for completion. This way, a user can refresh their browser w/o losing (needing to maintain) the state of the ReIndex.|||first thing that bothers me here is the fact that you are reindexing after every insert. why not just reindex the table overnight in a job.

I wonder what effect of (and I would never try this exepcially with a 10 second execution) is of putting your reindex into an insert trigger (which I use sparingly) on the table. Would the ASP page go about it's merry business before the trigger completed or would you still get held up on your page waiting on the trigger to execute.

By the way if your trying to insert and reindex at the same time on the same table I do imagine there would be some blocking even if you used multi-threading as implimented in JAVA.|||first thing that bothers me here is the fact that you are reindexing after every insert. why not just reindex the table overnight in a job.

I wonder what effect of (and I would never try this exepcially with a 10 second execution) is of putting your reindex into an insert trigger (which I use sparingly) on the table. Would the ASP page go about it's merry business before the trigger completed or would you still get held up on your page waiting on the trigger to execute.

By the way if your trying to insert and reindex at the same time on the same table I do imagine there would be some blocking even if you used multi-threading as implimented in JAVA.

I was curious myself about the trigger, if I created a trigger would the calling proc wait for the trigger to finish before returning to the application?

The reason I need to incrementally rebuild the index (I currently have an overnight job btw) is that when a user adds a new company they need a way to be able to search for it later, our search page does a phonetic search for companies (in case they misspelled it), if the job only runs nightly then the user would have to wait until the next day to see the company in the search results. This is an intranet application for about 50 users and adding companies is not going to be a very frequent process so I am not concerned with the strain on the server.

For anyone interested in implementing a phonetic search here is the pseudocode:

1. Split the company name into words
2. Translate each word into its phonetic representation (soundex is one option albeit bad - I implemented a double metaphone translation)
3. Remove duplicate phonetic words per company
4. Aggregate the occurrences of each phonetic
5. Give a score based on (in)frequency of each phonetic to aid in improved search results.
6. Increase the score for exact word matches
7. Increase the score for exact phrase matches
8. Return the results by score descending

There are about 40k unique phonetic words that are created as a result of this (out of a pool of close to a million companies).|||I was curious myself about the trigger, if I created a trigger would the calling proc wait for the trigger to finish before returning to the application?

Try it and let me know.|||Maybe I'm missing something really fundamental, but what on earth are you trying to accomplish? Reindexing is a performance issue, it can improve the internal structure of an index after massive inserts or deletes. Reindexing had better not have any effect at all on what rows are visible in the table!

Did I miss a meeting, or is this entire discussion moot?

-PatP|||Pat,

from what it sounds like his sp (COMPANY_REBUILD_INDEX) is a misnomer. It sounds like he is really doing all of his processing for his phonetic thingy majiggy and not a DBCC DBREINDEX. (which sounds like a lot of trouble to go through to tame some bad data entry).|||It's a nessary evil, we have over 100k council members that enter their own data, the search is used to help normalize the company name that they enter into their job history profile (each member can enter multiple job profiles based on their work history). The search had to be as flexible as possible and had to search on a per word basis and handle spelling mistakes, I think in the next version they want us to use a thesaurus to be able to match firm to company but in my mind THAT is going way overboard.|||did you try the trigger thing. i assumed you were't really doing a reindex as in dbcc. how did it go?