8

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 ?

J4N
  • 19,480
  • 39
  • 187
  • 340

2 Answers2

19

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")," / ")
urbanhusky
  • 1,336
  • 13
  • 35
  • `LookupSet` not available in ssrs-2005. Any similar solution? – maxx777 Aug 14 '14 at 10:36
  • Sorry, I don't know of any other method. – urbanhusky Aug 14 '14 at 14:12
  • Legend, LookupSet is pretty poorly documented - the first two parameters being 1 is the trick here. – meataxe Apr 05 '16 at 01:57
  • Theoretically, the first two parameters could be anything - they only have to be the same. I can't test or verify it right now but LookupSet takes the value (third parameter) whenever the first parameter matches the second - usually done between two different fields. – urbanhusky Jun 30 '16 at 18:48
3

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.

Community
  • 1
  • 1
Jeroen
  • 60,696
  • 40
  • 206
  • 339
  • Unfortunately I can't: 1. I receive only the ID from the sender, 2. I have to put this in a horizontal manner, 3. I already tried but I've to specify the width of every column :( – J4N Jun 28 '12 at 11:19
  • With option 1 I meant an *internal* parameter, not one the user has to fill but one that's filled by the dataset. – Jeroen Jun 28 '12 at 12:08
  • Oh? I don't know this kind of "internal parameter" do you have some information on this? – J4N Jun 28 '12 at 14:50
  • I've added some links in the answer (though you should invest some time in finding your way on the MSDN and other SO questions, which is easier and more effective than asking others). Hope it helps! – Jeroen Jun 28 '12 at 15:26
  • Only downvoting because the second answer solves much more elegantly. This served it's purpose in it's time, but trying to promote visibility on the cleaner alternative. – KyleMit Nov 03 '16 at 17:27
  • @KyleMit: Fair enough. I pondered deleting my answer even, but instead following the ping of your comment decided to rewrite my answer so that people will easily spot the "modern" solution, while keeping the original answer for OP's version (she/he tagged explicitly with ssrs-2008) so that souls stuck with that version too can see those options as well. – Jeroen Nov 03 '16 at 22:49
  • 1
    Perfect! Visibility! :) – KyleMit Nov 04 '16 at 00:03