Hide duplicate row and add Comma separated Column value in SSRS report

Question

User88744855 posted

suppose the data is stored in my table is like

EMP ID            EMP NAME                DEPARTMENT
----------        -----------------      ---------------------
 101                 KEITH                  MATH,ENGLISH
 102                 SAM                    ENGLISH,HINDI
 103                 KEITH                  HISTORY,MATH
 104                 SAM                    HISTORY
 105                 MARK                   ENGLISH

i want to display employee once and their distinct department the out put would be like

EMP ID             EMP NAME                    DEPARTMENT
----------        -----------------       ---------------------
101                 KEITH                  MATH,ENGLISH,HISTORY
102                 SAM                    ENGLISH,HINDI,HISTORY
103                 MARK                   ENGLISH

my report out put would look like above. i want to show distinct employee but their department will include all distinct department name separated by comma.

KEITH is in the department like  MATH,ENGLISH and HISTORY,MATH so keith record will show only once and his department will be like MATH,ENGLISH,HISTORY.....department MATH will not show twice.

i managed to hide duplicate row with expression like

=iif(Fields!EmpID.Value = Previous(Fields!EmpID.Value), True, False)

but not being able to concatinate distinct department with the respective employee. so please help me with idea that how to do all this in report level not in sql level. thanks

Answer 1

User-495744177 posted

Actually the data in you table is not normalize..  the DEPARTMENT column should contain one and only one value.. CHANGE your table structure and your life will become easy

EMPLOYEE TABLE
EMP ID            EMP NAME                

----------        -----------------      

 101                 KEITH               

 102                 SAM                 

 103                 KEITH               

 104                 SAM                 

 105                 MARK                

DEPARTMENT TABLE

DEPT ID           DEPARTMENT NAME
-------           ---------------
1                 MATH
2                 ENGLISH
3                 HINDI
4                 HISTORY

EMPLOYEE_DEPT TABLE

ID          EMP ID            DEPARTMENT ID
---         -------           -------------
1           101               1
2           101               2
3           102               2
4           101               4
5           103               3

Answer 2

User88744855 posted

i have no permission to access sql server. everything i need to manipulate in ssrs.