locked
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

    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
    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