Overview
SQL Services Reporting Services is a very powerful tool and with each new release, it grows in functionality. However many users find that the expression builder functions do not quite do what they need. Fortunately, Microsoft has provided a powerful means of extending the capabilities of a report, virtually without limit, by using the custom code capability. While custom code can access custom assemblies, the string manipulation functionality discussed here uses only the core .Net assemblies which are available by default on the Reporting server and so requires no explicitly referenced assemblies.

String Manipulation Using Custom Code
The SQL Server Reporting Services, Power View MSDN forum contains numerous questions asking how to extend the string manipulation capabilities of Reporting Services. In some cases, the solution can only be achieved through custom code. We will explore three different string manipulation scenarios requiring custom code in SSRS reports:

• Concatenating Data Column Values Into a Single String
• Counting Occurrences in a Concatenated String
• Sorting Elements of a Concatenated String

Concatenating Data Column Values Into a Single String
Occassionally a report author may have a need to display detail records in a grouped table in a single cell rather than separate rows.

Scenario
My dataset returns multiple rows of data for a given grouping with only one field (we will call it Grade) varying across those rows. I want my report to display a single line for a group with all of the values of Grade for that group displayed as a concatenated string in a single cell of each group row.

Solution
Add the below function to the custom code module of the report:

Private Num As String = ""
Private tempstr As String = ""

Function AggregateString(Group as String, Val as String) as String
  If Group = Num Then 
   tempstr = tempstr + ", " + Val
  Else
    Num = Group
    tempstr = Val
  End If
  Return tempStr
End Function

With this custom VB.Net function, you can create a delimited string that concatenates all of the specified detail records of a group using the RunningValue function.

=IIf(IsNothing(Fields!Grade.Value),"",RunningValue(Code.AggregateString(Fields!GroupUniqueID.Value,CStr(Fields!Grade.Value)),Max,"GroupScope"))

Counting Occurrences in a Concatenated String
Another example of extending the string functions of expression builder with custom code comes from the MSDN forums.

Scenario
I have a report that has two data sets that I am using a lookup to get the value needed. The returned value could be either "Completed", "In Progress", "Not Started". What I want to do is get the Sum of the "Completed" data elements returned. I have used this expression to look up the data set

=join(Lookupset(Fields!Job_Name.Value, Fields!Job_Name.Value, Fields!Activity.Value, "dataset2"),",")

When I use the above expression I get a column with the values concatenated within the text box like this:
-- Completed, Completed, Completed, In Progress, Not Started --

What I want to do it get the sum of the "Completed" occurrence's. So the answer I am looking for is "3".

Solution
You can use custom code in your report. Since you would be using all core VB.Net functionality you would not need to add any custom references. Here is what I did:

Function SumOccurrence(s As String, f As String) As Integer 
 Dim rtn As Integer = 0 
 Dim occ As String() = s.Split(",") 
 For Each el As String in occ 
 If el = f Then 
rtn = rtn + 1 
 End If 
 Next 
 Return rtn
End Function

So the number of "Completed" Activities could be found with:

=Code.SumOccurrences(join(Lookupset(Fields!Job_Name.Value, Fields!Job_Name.Value, Fields!Activity.Value, "dataset2"),","), "Completed")

The number of "In Progress" Activities could be found with:

=Code.SumOccurrences(join(Lookupset(Fields!Job_Name.Value, Fields!Job_Name.Value, Fields!Activity.Value, "dataset2"),","), "In Progress")

...And so on.

Sorting Elements of a Concatenated String
The final example from the MSDN forums is a little more advanced. The report designer needed to be able to sort the individual elements of a concatenated string dynamically in the report.

Scenario
I have a column in a table of my report that displays a series of values in a concatenated string. Can I use interactive sorting to sort the elements of the concatenated string?

Solution
You cannot do that with either sorting or interactive sorting. You could create your own interactive sorting for that by adding a small "sort" (maybe a 4or3) image in an appropriate location in proximity to the data to be sorted and set an action to run a report, the current report. That will trigger the rerunning of the report and give you the opportunity to set the value of a hidden parameter that indicates the order of the desired sort, ascending or descending.

The sort itself would need to be custom code that would take the original concatenated string, split it into an array of separate elements, sort it in the direction indicated by your parameter value, then concatenate the elements and return them. The below VB function will sort ascending and descending:

Function SortString (sort As String, dir As String) As String 
 Dim str As String() = Split(sort,",") 
 Dim rtn As String
 

If dir = "asc" Then 
Array.Sort(str) 
 Else 
Array.Sort(str) 
Array.Reverse(str) 
 End If
 


For Each s As String in str 
rtn = rtn + s + "," 
 Next
 
 Return rtn.Trim(",")
End Function

You call this function with an expression:

=Code.SortString([Concatenated String], Parameters!SortDir.Value)

Related Info

Sorting Elements of a Concatenated String
Counting Occurrences in a Concatenated String
Sorting Elements of a Concatenated String