Display comma Separated Values in SSRS Report
Issue-How to Display comma Separated Values in SSRS Report.
Being a SQL and BI Developer I am quite active in various SQL and BI related groups including my own SQL and BI group.So there one of the member of the group asked be the below Question.
IN SSRS I have a table like this
Ast it’s a common Requirement with lots of Geeks who are working with SSRS so I thought of writing a post on this in my Site.
Solutions-In order to get the values as comma separated first we will create a test table with above records.
CREATE TABLE Number(Id int, CellNo varchar(100))
INSERT INTO Number VALUES
( 1, '9xxx'),
SELECT * FROM Number
So once we have created a table we will create a Datasource and dataset in the SSRS Report and will show the same table in SSRS Report
Now in order to get the same format as required go to design view of Report.
Write the below Expression in the cell highlighted in Yellow.
=Join (LookUpSet (Fields!Id.Value, Fields!Id.Value, Fields!CellNo.Value, "DataSet1"), ",")
Preview the Report
Now you are able to see the desired Result with comma separated values Group on Id.But you will notice that there are duplicates rows coming .
So, In order to Remove duplicates Records in SSRS .
Go to design view of Report
Select the second Row
Click on Row Visibility
Select show and hide based on Expression as in Screenshot.
Write the below Expression
=iif(Fields!Id.Value = Previous(Fields!Id.Value), True, False)
Preview the Report.
We will get the exact desired format without any duplicate Records.
Note:If you want to Get the same format in SQL check my older post on same.