Skip to content

Latest commit

 

History

History
128 lines (85 loc) · 6.88 KB

report-builder-functions-multilookup-function.md

File metadata and controls

128 lines (85 loc) · 6.88 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom
Multilookup function in a paginated report
The Multilookup Function returns the set of first-match values for the specified set of names from a dataset that contains name/value pairs in a paginated report in Report Builder.
kfollis
kfollis
09/25/2024
reporting-services
report-design
how-to
updatefrequency5

Report Builder functions - Multilookup function in a paginated report (Report Builder)

[!INCLUDEssrs-appliesto] [!INCLUDE ssrs-appliesto-ssrs-rb] [!INCLUDE ssrs-appliesto-pbi-rb] [!INCLUDE ssrb-applies-to-ssdt-yes]

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

Note

[!INCLUDEssRBRDDup]

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 (Report Builder and SSRS) 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 (Report Builder and SSRS). 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 (Report Builder and SSRS).

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.

For more information, see Aggregate Functions Reference (Report Builder and SSRS) and Expression Scope for Totals, Aggregates, and Built-in Collections (Report Builder and SSRS).

Examples

A. Use MultiLookup function

Assume a dataset called "Category" contains the field CategoryList, which is a field that contains a comma-separated list of category identifiers, 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")),  
   ", ")

B. Use MultiLookup with multivalue parameter

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

Related content