none
SSRS concatenate field in one string delimited by comma - without SQL

    Question

  • Hi,

    i have a dataset in SSRS report as below:

    Cat   subcat
    cat1   sub1
    cat1   sub2
    cat1   sub3


    There is such requirement that I need to show in report like this:
    Cat         SubCat
    cat1        sub1, sub2, sub3


    I know we can easily do it in SQL but unfortnately the dataset does not come from DB.

    How can I achieve the similar effect in Report RDL?
    Also my report need to be uplaoded to CRM Online and no custom function is supported.

    Wednesday, December 21, 2011 4:56 AM

Answers

  • Hey Henry,

    Try the following expression within a textbox in an SSRS report:

    =Join (LookUpSet (Fields!Cat.Value, Fields!Cat.Value, Fields!SubCat.Value, "DataSet1"), ",")

    I have assumed that your dataset is still having the default name of "DataSet1" and that your fields are named as Cat and SubCat.

    This would need SSRS 2008 to work though.

    Let me know if this works for you. HTH.

    Cheers,

    IceQB


    Please mark correct answers :)
    Wednesday, December 21, 2011 7:51 AM

All replies

  • Hey Henry,

    Try the following expression within a textbox in an SSRS report:

    =Join (LookUpSet (Fields!Cat.Value, Fields!Cat.Value, Fields!SubCat.Value, "DataSet1"), ",")

    I have assumed that your dataset is still having the default name of "DataSet1" and that your fields are named as Cat and SubCat.

    This would need SSRS 2008 to work though.

    Let me know if this works for you. HTH.

    Cheers,

    IceQB


    Please mark correct answers :)
    Wednesday, December 21, 2011 7:51 AM
  • Thanks.
    I have another challenge,

    Cat   subcat
    cat1   sub1
    cat1  
    cat1  
    cat1  
    cat1   sub3


    Join (LookUpSet (Fields!Cat.Value, Fields!Cat.Value, Fields!SubCat.Value, "DataSet1"), ",")

    return sub1, , , ,sub3

    How do I eliminate the empty / null value?

    Tuesday, December 27, 2011 3:59 AM
  • Hi,

    Try replacing them:

    =REPLACE(Join(LookUpSet (Fields!Cat.Value, Fields!Cat.Value, Fields!SubCat.Value, "DataSet1"), ","), " ,", "")
    


    Regards,

    Asim Bagwan

    Kindly mark the replies as Answers if they help!

    Tuesday, December 27, 2011 7:18 AM
  • have a challenge on the same issue .. what if

    Cat   subcat
    cat1   sub1
    cat1   sub2
    cat1   sub2
    cat1   sub2
    cat1   sub3


    the above there is a duplicate sub2 , i want DISTINCT
    Cat         SubCat
    cat1        sub1, sub2, sub3

    Tuesday, March 20, 2012 9:04 PM
  • I too have this same question. How do I use something like "distinct" in VB? 

    Thanks,
    Jennifer

    Thursday, June 07, 2012 7:45 PM
  • Would concatenating in T-SQL work for you

    http://social.msdn.microsoft.com/Forums/en-SG/sqlreportingservices/thread/11f53a73-0dce-422e-b941-d217f9b151db


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 07, 2012 8:08 PM