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.

Syntax

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"), ",")

Usage

• The data type of the SourceExpression and the DestinationExpression should be same.
• When the Lookup function is used as a value expression in a data region, the expression is evaluated for each row or repeated data of the data region's dataset.
• The Lookup function returns one value if found, and null if no rows are found in the Lookup dataset.
• The Lookup expressions can be a part of aggregated expressions. A user can use the Lookup function in a table group or table header or footer, and sum all values for the table.

Limitations

• Only "=" comparison is supported between SourceExpression and DestinationExpression.
• Non-aggregate expressions such as multiply, mod, AND and OR, are not allowed in the comparison criteria.
• Only one level of Lookup is allowed, that is, nested Lookup functions are not supported.