Concatenating Data Column Values Into a Single String

Concatenating Data Column Values Into a Single String

The Request
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.

The Solution
I did a similar thing in a report using TFS data. My grouping was by backlog item, each having 1 or more tasks, resulting in one row per task. My approach required custom code. I added the below custom code to the code module for the report:

 

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

Then, in the cell where the aggregated string will appear I added a formula similar to the below. I have modified it to reflect your fields:

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

 


 

See Also

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



Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (3 items)