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