SSRS: Using lookupset to get multiple fields

Question

I have two datasets:

My main dataset (Students) looks like this:

Student Name      | PID
Anakin SkyWalker  | 1
Obi-Wan Kenobi    | 2

And my second dataset (Awards) looks like this:

PID  | Xtrnl_Award_Type | Xtrnl_Award_Date
1    | BS               | 200912
2    | BA               | 200605
2    | MS               | 200905

What I want to get is this:

Student Name      | Awards
Anakin SkyWalker  | BS - 200912
Obi-Wan Kenobi    | BA - 200605, MS - 200905

The LookupSet function can only return one field in the second dataset. Is there some other way I can get the results I want? The second dataset is on another server.

Answer 1

You can use an expression as the target Dataset field:

=Join(LookupSet(Fields!PID.Value
    , Fields!PID.Value
    , Fields!Xtrnl_Award_Type.Value & " - " & Fields!Xtrnl_Award_Date.Value
    , "Awards"), ", ")

Works for me based on your data: