The LookupSet function returns multiple row values from a specified dataset, so this function is used for one-to-many relationship in a data. The fields of the dataset returned by the LookupSet function behave as regular dataset fields that you can use in functions/aggregates within the scope of the data region.
The following data regions can use the LookupSet function in the Value property - Tablix, Table, Classic Chart, BandedList, List, and Sparkline.
The basic syntax of the Lookup expression is as follows.
LookupSet(<SourceExpression>, <DestinationExpression>, <ResultExpression>, <LookupSetDataset>)
• SourceExpression: An expression that is evaluated in the current scope and that specifies the name or key to look up.
• DestinationField: An expression that is evaluated for each row in a dataset and that specifies the name or key to match on.
• ResultExpression: An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve.
• LookupSetDataset: A constant that specifies the name of a dataset in the report. For example, "ContactInformation".
The report below shows information on addresses for each employee and displays the addresses as string separated by commas. To display all addresses for each employee in a string separated by commas, we need to use the Join function in the expression with the LookupSet function.
For example:
=LookupSet(Fields!CategoryID.Value, Fields!CategoryID.Value, Fields!UnitsInStock.Value, "Products")
=Join(LookupSet(Fields!CategoryID.Value, Fields!CategoryID.Value, Fields!UnitsInStock.Value, "Products"), ",")
