Reporting Services: Combining All Fields in a Dataset

Question

In the report, I have a data set with a filter (based on the MultiValue parameter).

This dataset contains two fields: Id and Name.

I need to display somewhere the concatenation of all the names:

Name1 / Name2 / Name3 

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

I also looked into the user code, but I did not find anything.

How can I do it?

Answer 1

SSRS-2008 R2 and higher ...

1. Using LookupSet
If you are outside the 2008 OP version, there is a good solution:

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

The credit for this answer using the LookupSet solution fully applies to the @cityhusky answer .

---

SSRS-2008 and below ...

I keep this answer because it combines @urbanhusky's solution with the solutions available to poor souls stuck with SSRS OS or lower.

There are only three “options” in SSRS 2008, as far as I can see, each with its own flaw. The first, perhaps the least hacker.

2. Additional parameter
Create an internal parameter (for example, "NameParameter", see this SO or MSDN answer ) with Allow multiple values. Set the default value for the parameter in the Name field of your data set. Then use the function =Join(Parameters!NameParameter.Value, " / ") to show the combined names in the text box.

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

3. Using the list
Create a list and drag it into the "Name" field. If necessary, also group the name.

The disadvantage here is that the (AFAIK) list cannot be displayed horizontally.

4. Using the matrix
Oh boy, this is real ugly. However, here it goes: create a matrix, drag the "Name" field into the column heading and hide the first column, as well as the second row (to display data).

The main disadvantage is that it is a hack (and quite some superfluous), plus you have to manually trim the last separator character with an expression.

Answer 2

I might be a little late for this, but for those interested in this, there is a pretty simple way to SSRS:

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