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
