Join column values as x, y and z–SSRS

Recently, I got a requirement to combine the contact names into a single column. If there are 2 contacts then Contact1 and Contact2 and if more than 2 contacts then format should be like Contact1, Contact2, …. and ContactN.

There is no direct function to achieve this requirement in SSRS. We have Join function by this we could get “,” separated contact names but for the last contact name we should get “And”.

After spending sometime, I have achieved this by using below SSRS OOB formals as below:

=StrReverse(replace(StrReverse(join(lookupset(Fields!AccountID,Fields!AccountID, Fields!ContactNames, “Contact_Dataset”),”, “)),”,”,”dna “,1,1))

Formula Details:

join(lookupset(Fields!AccountID,Fields!AccountID, Fields!ContactNames, “Contact_Dataset”),”, “)

This formula joins the Contacts Names from “Contact_Dataset’ and separate by “,”

replace(StrReverse(ContactNamesSeparatedByAboveFormula),”,”,”dna “,1,1)

By using “Replace” formula we could replace the first occurrence of specified string with result string. In our requirement, we should replace the last “,” so I have reversed the ContactNames and replaced the first “,” with “dna” (Revers of “and”).

Finally reverse the result string. By this we could get the required result.

 =StrReverse(replace(StrReverse(join(lookupset(Fields!AccountID,Fields!AccountID, Fields!ContactNames, “Contact_Dataset”),”, “)),”,”,”dna “,1,1))

Hope this helps someone  

Please comment if anyone find easy solution for this requirement. Thanks.