CODEWITHSUNDEEP
sql-serverreporting-services
AaronSieb
AaronSieb

Reputation: 8256

How do I display a dataset or group as a comma delimited list in SSRS?

In my SQL database, I have a one-to-many relationship, something like this:

Teacher  Student
John     Alex
John     Mike
John     Sean
Bob      Jack
Gary     George
Gary     Paul

I'd like to display a table listing each teacher, with their students as a comma-delimited list, like this:

Teacher  Students
John     Alex, Mike, Sean
Bob      Jack
Gary     George, Paul

This question describes how to do this on the SQL Server end, but is there a way to do this on the SSRS side of things?

Upvotes: 5

Views: 13897

Answers (3)

Jerry
Jerry

Reputation: 4547

I've found a simpler, straight-forward method to do this -- add an expression as follows:

=Join(LookUpSet(Fields!TeacherName.Value, 
    Fields!TeacherName.Value, 
    Fields!StudentName.Value, 
    "YourTeacherDataSet"), ",")

Upvotes: 6

AaronSieb
AaronSieb

Reputation: 8256

This StackOverflow answer presents one technique for accomplishing this:

String aggregation in SSRS 2005

The downside to this technique is that it uses shared variables in a code module, which may cause concurrency issues if the report is hosted on a network.

I've also come across another work-around:

Create a custom function, such as GetStudentList(TeacherId As Integer, ConnectionString As String), which is intended to return the list of students based on the specified teacher.

This function can then be written to open a connection to the database, run a query, process the results, and then return them. But that means opening the connection and running the query for every row, which seems like a lot of overhead for this type of formatting (not to mention the need to pass in a Connection String).

This is based largely on an Experts' Exchange article.

Upvotes: 0

gbn
gbn

Reputation: 432180

Add a grouping on teacher, and use the .net Join to append the detail rows

Join is demonstrated for multi-value parameters in BOL... so in theory it can be used for the result dataset

Join

Join and multi-value parameters

Upvotes: 1

Related Questions