Menu
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 ?
639
In the Report Data pane, expand the Parameters node, right-click the report parameter that was automatically created for the dataset parameter, and then click Parameter Properties. In the General tab, select Allow multiple values to allow a user to select more than one value for the parameter.
The ReportItems built-in collection is the set of text boxes from report items such as rows of a data region or text boxes on the paginated report design surface.
At the top-right, from the list, select Join Dataset. In the Data Set Name box, type a name for the Join data set. In the Query box, enter a SQL query that combines fields from both data sets.
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")," / ")
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.
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.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
