SSRS Displaying Comma Delimited or Distinct Values in Single Column

0
votes

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!

1
reporting-servicesssrs-2008ssrs-2008-r2
Are you using that expression in a calculated field? You are using any of the error listed functions in a calculated field expression. The expression you are trying should work in a cell – alejandro zuleta
Yes, I'm using this expression in a calculated field. I'm using SQl 2008 R2 and VS 2012. – BIReportGuy
Don't use it as calculated field, use it in your column. If you need further help I'll post an answer. – alejandro zuleta
Ok, please provide more details of what you mean. Thanks. – BIReportGuy

1 Answers

2
votes

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.