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

  • 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

    Wednesday, March 21, 2012 2:29 PM

All replies

  • 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

    EMP ID            EMP NAME                
    ----------        -----------------      
     101                 KEITH               
     102                 SAM                 
     103                 KEITH               
     104                 SAM                 
     105                 MARK                
    -------           ---------------
    1                 MATH
    2                 ENGLISH
    3                 HINDI
    4                 HISTORY
    ID          EMP ID            DEPARTMENT ID
    ---         -------           -------------
    1           101               1
    2           101               2
    3           102               2
    4           101               4
    5           103               3
    Wednesday, March 21, 2012 3:03 PM
  • User88744855 posted

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

    Thursday, March 22, 2012 2:29 AM