Use Split, Join, and another function in SSRS

Question

I have a field in SQL Server that contains an comma separated list. Here are 2 examples:

select 'ex1,ex2,ex3' as str union all
select 'ax1,ax2' 

In my report, I have to transform all of these values (5 in this case) using a function. In this question I will use Trim, but in actuality we are using another custom made function with the same scope.

I know how I can split every value from the string and recombine them:

=Join(Split(Fields!str.Value,","),", ")

This works great. However, I need to execute a function before I recombine the values. I thought that this would work:

=Join( Trim(Split(Fields!VRN.Value,",")) ,", ")

However, this just gives me an error:

Value of type '1-dimensional array of String' cannot be converted to 'String'. (rsCompilerErrorInExpression)

I can't personally change the function that we use.

How do I use an extra function when dealing with both an split and a join?

Answer 1

You can use custom code to include all the logic (Split->Custom Code->Join).

Make adjustments inside the loop to call your custom function instead of trim

Public Function fixString (ByVal s As String) As String

Dim  mystring() As String  

mystring = s.Split(",")

For index As Integer = 0 To mystring.Length-1
    mystring(index) = Trim(mystring(index))
Next 

Return Join(mystring, ",")

End Function

To call the custom code use the following expression

Code.fixString( Fields!VRN.Value )