ssrs,reporting services Look up function only picking the first value

0
votes

I have two sets with two different values with unique column identifier.

When i use lookup The result expression is only picking the first value even though the matching is in the third row.

DataSet 1: i have result set like Column1 column2 column3 1 2-1 x 2 2-2 xxx 3 2-3 xxxx

DataSet 2: i have result set like Column1 column2 column3 1 2-1 4 2 2-2 5,6,7 3 2-3 8,9,10

My lookUp expression is =Lookup(Fields!column2.Value = "2-3", Fields!column2.Value = "2-3" , Fields!List_Output.Value, "DataSet02")

Result supposed to get 8,9,10 but the result is showing 4 which is first value.

Not sure of the exact reason though i think i have used look up before and it worked for other reports but not for this..

Does groupings has anything to do with the reports if so my tablix doesn't have any groupings at all.

Tried may blogs but couldn't find any answer.

Any help would be much appreciated.

Thanks, VR

1
reporting-services

1 Answers

1
votes

A lookup() will only ever return a single value. A lookupset() returns a set (array) of values to be used with either a join() to join them into a list or custom code to aggregate it.

More information on lookup(): https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookup-function

More information on lookupset(): https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookupset-function

More information on aggregating a lookupset(): https://itsalocke.com/aggregate-on-a-lookup-in-ssrs/