Share via


Multilookup Function

Returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs.

Syntax

Multilookup(source_expression, destination_expression, result_expression, dataset)

Parameters

  • source_expression
    (VariantArray) An expression that is evaluated in the current scope and that specifies the set of names or keys to look up. For example, for a multivalue parameter, =Parameters!IDs.value.

  • destination_expression
    (Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!ID.Value.

  • result_expression
    (Variant) An expression that is evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!Name.Value.

  • dataset
    A constant that specifies the name of a dataset in the report. For example, "Colors".

Return

Returns a VariantArray, or Nothing if there is no match.

Remarks

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.

    • Report parameters.

    • The Report.Language property.

Example

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.

ID

Name

1

Accessories

2

Bikes

3

Clothing

4

Components

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.

ColorID

Color

1

Red

2

Blue

3

Green

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".

=Join(MultiLookup(Parameters!MyColors.Value,Fields!ColorID.Value,Fields!Color.Value,"ProductColors"),", ")

See Also

Reference

Expression Examples (Reporting Services)

Dataset Properties Dialog Box, Options

Concepts

Working with Data Types in Expressions (Reporting Services)

Calculating Totals and Other Aggregates (Reporting Services)

Using Built-in Report and Aggregate Functions in Expressions (Reporting Services)

Using Single-Value and Multivalue Parameters