Sometimes, we need to aggregate strings in SSRS designer. In situations, when we are using Report Model as data source this is very much needed.
There are many Aggregation functions available in SSRS, but to implement string concatenation/aggregation, we can’t directly use any of the provided function directly. But, by using SSRS custom code and RunningValue aggregate function we can achieve this as shown below.
For reference the same report is attached herewith. You can find the report below.
Step-1 Create a dataset.
Note:- This example is just created to simulate the situation.
DECLARE @tab TABLE ( id INT, name NVARCHAR(100), phoneNumber NVARCHAR(100))
INSERT INTO @tab (id,name,phonenumber)
, (1,'Sachin','26612345' )
SELECT a.id ,a.name ,a.phoneNumber FROM @tab a
Step-2 Create a report with RowGrouping on ID.
Step-3 Add custom code
Dim Num As Integer = 0
Dim tempstr As String = ""
Function AggregateString(ID as Integer, PhoneNumber as String)
If ID = Num Then
tempstr = tempstr + "," + PhoneNumber
Num = ID
tempstr = PhoneNumber
Step-4 Add colum with following expression
Step-5 Run Report.
Here, we can see the concatenated rows as shown in below.