SSRS expression with multiple values from dataset via parameter

Question

In my placeholder expression, I looking to pull multiple values from a dataset variable which relate to the parameter multiple selected values. My parameter labels are locations (Location 1, Location 2, Location 3, etc...) and the parameter values are IDs (ID1, ID2, ID3, etc...). In my dataset, I have abbreviated versions of the locations which is what I want to display in my SSRS report (Loc 1, Loc 2, Loc 3).

I would like the values to be separated by a comma.

I know I can get the parameter labels with this code:

=Join(Parameters!ParameterName.Label, ",")

So I tried something similar such as:

=Join((Fields!ABBREV.Value, ", "), "Dataset1")

and

=Join((Fields!ABBREV.Value,"Dataset1"), ", "), )

but neither worked

In the end, I would like to see the list of abbreviated locations. For example, if the user selects Location 5, Location 7 and Location 9 in the parameter, my expression should show:

loc 5, loc 7, loc 9

What is the right expression for this? Thanks in advance.

EDIT SOLUTION:

This worked...

=JOIN(MULTILOOKUP(Parameters!ParameterName.Value, Fields!ID.Value, Fields!ABBREV.Value, "DataSet1"), ", ")

Hannover Fist Hannover Fist · Accepted Answer · 2019-10-24T19:10:18

I think it will work if you use LOOKUPSET.

=JOIN(LOOKUPSET(1, 1, Fields!ABBREV.Value, "DataSet1"), ", ")

Using 1 for the LOOKUP fields will return all records in Dataset1.

SSRS expression with multiple values from dataset via parameter

1 Answers