CODEWITHSUNDEEP
reporting-services
Michael Robinson
Michael Robinson

Reputation: 1132

SSRS: Using lookupset to get multiple fields

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.

Upvotes: 11

Views: 29165

Answers (2)

Michael Robinson
Michael Robinson

Reputation: 1132

I just realized I can get what I wanted by adding a calculated field to my second data set that combined the two fields.

Upvotes: 0

Ian Preston
Ian Preston

Reputation: 39566

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:

enter image description here

Upvotes: 13

Related Questions