Search code examples
reporting-servicesssrs-2008ssrs-2008-r2

SSRS Displaying Comma Delimited or Distinct Values in Single Column

I have a relationship like this:

enter image description here

I'd like to display the Acctnbr field in SSRS like this as a single field:

enter image description here

I have tried expressions like this

=Join(LookUpSet(Fields!Baseacctnbr.Value, 
    Fields!Baseacctnbr.Value, 
    Fields!Acctnbr.Value, 
    "DataSet1"), ",")

and I get an error message that reads:

Aggregate, Rownumber, runningvalue, previous and lookup functions cannot be used in calculated field expressions.

I can get the comma delimited field from SQL using the STUFF function, but my SSRS report is grouping and when I group I loose the SQL code.

How can I get this to work? Much appreciated!

Solution

  • Using this dataset to test I recreated your scenario.

    enter image description here

    Try using your expression in a column of your tablix:

    enter image description here

    I've added the expression you posted in the cell is highlighted.

    It will preview the following table.

    enter image description here

    Let me know if this can help you.