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

Question

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?

score 6 · Accepted Answer · answered May 28 '15 at 23:20

6

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"), ",")

answered May 28 '15 at 23:20

Jerry

4,507
9
50
79

This works as expected. Thanks. Some more details on [LookupSet and Join fucntion](https://msdn.microsoft.com/en-us/library/ee240819.aspx) – Pushkar Jul 23 '15 at 12:35

score 1 · Answer 2 · answered Oct 16 '08 at 04:18

1

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

answered Oct 16 '08 at 04:18

gbn

422,506
82
585
676

This looks promising, gbn. But I'm having trouble figuring out how to get the field values for a specific group as an array. Any ideas? – AaronSieb Oct 16 '08 at 14:39
As a concrete example I've tried putting this expression in the group footer: =Join(Fields!Student, ", ") . This compiled, but resulted in a #Error. – AaronSieb Oct 16 '08 at 14:49
Sorry: looks like I'm wrong. Just when you think you know RS. Ideas: 1. use a matrix to turn rows into columns but you lose the nice CSV format 2. Subreport where you load the subset as a parameter and use JOIN on that – gbn Oct 16 '08 at 17:45
The Subreport is promising, but passing the field values into a multi-value parameter doesn't seem to be possible. The matrix is close, but lacks some of the features of a comma separated list (such as word wrap). – AaronSieb Oct 16 '08 at 18:08

score 0 · Answer 3 · edited May 23 '17 at 12:02

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.

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

3 Answers3