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
