Using Custom Code in SSRS Reports for Advanced String Operations

  • Article

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 Thenrtn = rtn + 1  End If Next Return rtnEnd 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" ThenArray.Sort(str)  ElseArray.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

See Also

List of Award Winning TechNet Guru Articles
SQL Server Reporting Services Portal