Postal Codes 10001-10005 as a parameter

Dear all
I like to implement a parameter in SSRS SP2, that accepts values like
10001-10005 as postal code. This should be translated to 10001, 10002,10003,
10004, 10005.
I tried to realize this with the following code:
Function SplittingCodes(ByVal s As String) As String
Dim ar As String()
Dim subar As String()
Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder
ar = s.Split(","c)
For i As Integer = 0 To ar.Length - 1
ar(i) = ar(i).Trim()
If ar(i).Contains("-") Then
subar = ar(i).Split("-"c)
For j As Integer = CInt(subar(0)) To CInt(subar(1))
sb.Append(" '")
sb.Append(j)
sb.Append("'")
If j <> CInt(subar(1)) Then sb.Append(",")
Next
Else
sb.Append("'")
sb.Append(ar(i))
sb.Append("'")
End If
If i <> ar.Length - 1 Then sb.Append(",")
Next
Return sb.ToString()
End Function
I created an sql query that uses the parameter like this:
SELECT SUM (a) as test FROM table WHERE co IN (@.pcode)
And in the dataset tab on Parameters I used this code for the parameter
@.pcode:
=Code.SplittingCodes(Parameters!pc.Value)
Unfortunately the reports gives an empty dataset back.
Any help would be apreciated!
Thanks,
MarcHello Rombooth,
The root cause of this issue is that the sql statement you use in the
report.
My suggestion is that you could create a function in the sql server side to
split the string. And you could use this function in the sql statement.
You could refer this article to create the split function.
http://www.devx.com/tips/Tip/20009
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei! This solved my problem.
Best Regards,
Marc|||Hello Marc,
My pleasure!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.