SSRS combining values from multiple rows (lookupset?)

0
votes

I'm trying to display the programs a contact is attached to. When the contact is attached to multiple programs, I can only display it by having multiple rows, but I'd like to have it in one.

Currently my data looks like this (it's currently grouped by User ID): enter image description here

However, I'd like to group the data like so: enter image description here

I've tried a couple things, the most promising being lookupset - My code looked like this, but it just throws an "#Error" when I run the report:

=JOIN(LookupSet(Fields!UserID,Fields!UserID,Fields!PMT_ABB_Name,"DataSet1"), ", ")

Any advice?

1
sqlreporting-servicesreportbuilder
have you tried using the expression without the join: LookupSet(Fields!UserID,Fields!UserID,Fields!PMT_ABB_Name,"DataSet1") – SuperSimmer 44
Have you tried grouping by UserID, Name and Email? – SS_DBA

1 Answers

0
votes

You need to group on UserId - assuming it's unique.

Then in your Program text box, use the expression:

=JOIN(Fields!Program.value,", ")