Returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs.
Use Multilookup to retrieve a set of values from a dataset for name-value pairs where each pair has a 1-to-1 relationship. MultiLookup is the equivalent of calling Lookup for a set of names or keys. For example, for a multivalue parameter that is based on primary key identifiers, you can use Multilookup in an expression in a text box in a table to retrieve associated values from a dataset that is not bound to the parameter or to the table.
Multilookup does the following:
Evaluates the source expression in the current scope and generates an array of variant objects.
For each object in the array, calls Lookup Function and adds the result to the return array.
Returns the set of results.
To retrieve a single value from a dataset with name-value pairs for a specified name where there is a 1-to-1 relationship, use Lookup Function. To retrieve multiple values from a dataset with name-value pairs for a name where there is a 1-to-many relationship, use LookupSet Function.
The following restrictions apply:
Multilookup is evaluated after all filter expressions are applied
Only one level of look-up is supported. A source, destination, or result expression cannot include a reference to a lookup function.
Source and destination expressions must evaluate to the same data type.
Source, destination, and result expressions cannot include references to report or group variables.
Multilookup cannot be used as an expression for the following report items:
Dynamic connection strings for a data source.
Calculated fields in a dataset.
Query parameters in a dataset.
Filters in a dataset.
The Report.Language property.
Assume a dataset called "Category" contains the field CategoryList, which is a field that contains a comma-separated list of category identifers, for example, "2, 4, 2, 1".
The dataset CategoryNames contains the category identifier and category name, as shown in the following table.
To look up the names that correspond to the list of identifiers, use Multilookup. You must first split the list into a string array, call Multilookup to retrieve the category names, and concatenate the results into a string.
The following expression, when placed in a text box in a data region bound to the Category dataset, displays "Bikes, Components, Bikes, Accessories":
=Join(MultiLookup(Split(Fields!CategoryList.Value,","), Fields!CategoryID.Value,Fields!CategoryName.Value,"Category")), ", ")
Assume a dataset ProductColors contains a color identifier field ColorID and a color value field Color, as shown in the following table.
Assume the multivalue parameter MyColors is not bound to a dataset for its available values. The default values for the parameter are set to 2 and 3. The following expression, when placed in a text box in a table, concatenates the multiple selected values for the parameter into a comma-separated list and displays "Blue, Green".