Geek Answers Handbook

How to 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 would like to display a table that lists all the teachers, with their students as a comma-separated list, for example:

  Teacher students
 John Alex, Mike, Sean
 Bob jack
 Gary George, Paul

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

+3
sql-server reporting-services
source share
3 answers

This StackOverflow answer is one way to achieve this:

Row aggregation in SSRS 2005

The disadvantage of this method is that it uses common variables in the code module, which can cause concurrency problems if the report is hosted on the network.

I also ran into another problem:

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

You can then write this function to open a database connection, run a query, process the results, and then return them. But this means opening a connection and executing a query for each line, which seems like a lot of overhead for this type of formatting (not to mention the need to pass in the connection string).

This is largely based on an article by Exchange experts .

0
source share

I found a simpler, direct method for this: add an expression as follows:

=Join(LookUpSet(Fields!TeacherName.Value, Fields!TeacherName.Value, Fields!StudentName.Value, "YourTeacherDataSet"), ",")
+5
source share

Add a group to the teacher and use .net Join to add detail lines.

The connection is demonstrated for multi-valued parameters in BOL ... therefore, theoretically, it can be used to set the result data

Join

Combine and multi-valued options

+1
source share

More articles:


All Articles