Reporting services: Join all field on a dataset

Question

In a report, I've a dataset with a filter(based on a MultiValue parameter).

This dataset contains two field: Id and Name.

I need to display somewhere the concatenation of all names:

Name1 / Name2 / Name3

The problem is that the join method works only on array, and then I cannot specify a dataset as value.

I looked in custom code too, but I didn't found anything working.

How should I do this ?

Solution 1

SSRS-2008 R2 and higher...

1. Using LookupSet
If you're beyond the 2008 version OP has, there exists a good solution:

=Join(LookupSet(1, 1, Fields!Name.Value, "DatasetName"), " / ")

Credit for this answer using the LookupSet solution goes entirely to @urbanhusky's answer.

---

SSRS-2008 and lower...

I'm keeping this answer though because it aggregates @urbanhusky's solution with the solutions available to poor souls stuck with OP's version of SSRS and below.

In SSRS 2008 there's only three "options" as far as I can see, each with its own downside. The first one's probably the least hackish.

2. Extra parameter
Create an internal parameter (e.g. "NameParameter", see this SO answer or MSDN) with Allow Multiple Values. Set the default value of the parameter to the Name field from your dataset. Then use the function =Join(Parameters!NameParameter.Value, " / ") to show the joined names in a textbox.

This may be your best bet, but if there are a lot of values the parameter may not work very well.

3. Use a List
Create a List and drag/drop the Name field to it. If necessary, group on the Name as well.

The disadvantage here is that (AFAIK) the list can't be made to show horizontally.

4. Use a Matrix
Oh boy, this one's real ugly. Nonetheless, here goes: create a matrix, drag the Name field to the column header, and hide the first column as well as the second row (for displaying the data).

The main disadvantage is that it's a hack (and quite some overkill), plus you'll have to trim the last seperator character manually with an expression.

Solution 2

I may be a bit late for this but for anyone that's interested in this, there is a rather easy way of doing this in SSRS:

=Join(LookupSet(1,1,Fields!Name.Value, "DatasetName")," / ")