Display comma Separated Values in SSRS Report

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 

Id cellNo 
1 9xxx
1 8xxx
1 7xxx
2 9xxx
2 8xxx
3 7xx
4 9xxx

 output Required.

Id cellNo
1 9xxx,8xxx,7xxx
2 9xxx,8xxx
3 7xxx
4 9xxx

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

(1, '8xxx'),

(1, '7xxx'),

(2, '9xxx'),

(2,' 8xxx'),

(3, '7xxx'),

(4,'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)

Click ok

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.

http://www.sqlandssrssolutions.com/search?q=comma+separated