报表生成器函数 - Lookup 函数Report Builder Functions - Lookup Function

从包含名称/值对的数据集返回指定名称的第一个匹配值。Returns the first matching value for the specified name from a dataset that contains name/value pairs.

备注

在 SQL Server Data Tools 中,你可以在报表生成器和报表设计器中创建和修改分页报表定义 (.rdl) 文件。You can create and modify paginated report definition (.rdl) files in Report Builder and in Report Designer in SQL Server Data Tools. 每个创作环境提供了不同的方式来创建、打开和保存报表和相关项。Each authoring environment provides different ways to create, open, and save reports and related items.

语法Syntax

  
Lookup(source_expression, destination_expression, result_expression, dataset)  

参数Parameters

source_expressionsource_expression
(Variant) 在当前作用域中计算结果并指定要查找的名称或键的表达式。(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. 例如,=Fields!ProdID.ValueFor example, =Fields!ProdID.Value.

destination_expressiondestination_expression
(Variant) 针对数据集中的每行计算结果并指定要匹配的名称或键的表达式。(Variant) An expression that is evaluated for each row in a dataset and that specifies the name or key to match on. 例如,=Fields!ProductID.ValueFor example, =Fields!ProductID.Value.

result_expressionresult_expression
(Variant) 针对数据集中的行(其中, source_expression = destination_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. 例如,=Fields!ProductName.ValueFor example, =Fields!ProductName.Value.

数据集 (dataset)dataset
指定报表中数据集的名称的常量。A constant that specifies the name of a dataset in the report. 例如,“Products”。For example, "Products".

返回Return

返回 Variant,如果没有匹配项,则返回 NothingReturns a Variant, or Nothing if there is no match.

备注Remarks

使用 Lookup 从指定的数据集中为名称-值对(每对具有 1 对 1 关系)检索值。Use Lookup to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. 例如,对于表中的 ID 字段,可以使用 Lookup 从未绑定到该数据区域的数据集检索对应的名称字段。For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region.

Lookup 执行下列操作:Lookup does the following:

  • 计算当前作用域中源表达式的结果。Evaluates the source expression in the current scope.

  • 根据指定数据集的排序规则,在应用筛选器后对指定数据集的每行计算目标表达式的结果。Evaluates the destination expression for each row of the specified dataset after filters have been applied, based on the collation of the specified dataset.

  • 对于源表达式和目标表达式的第一个匹配,计算数据集中该行的结果表达式。On the first match of source expression and destination expression, evaluates the result expression for that row in the dataset.

  • 返回结果表达式值。Returns the result expression value.

若要为单个名称或键字段检索多个值(具有 1 对多关系),请使用LookupSet 函数(报表生成器和 SSRS)To retrieve multiple values for a single name or key field where there is a 1-to-many relationship, use LookupSet Function (Report Builder and SSRS). 若要为一组值调用 Lookup,请使用 Multilookup 函数(报表生成器和 SSRS)To call Lookup for a set of values, use Multilookup Function (Report Builder and SSRS).

存在以下限制:The following restrictions apply:

  • 在应用所有筛选表达式后计算Lookup 的结果。Lookup is evaluated after all filter expressions are applied.

  • 只支持一个级别的查找。Only one level of lookup 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. 返回类型和计算后的结果表达式的数据类型相同。The return type is the same as the data type of the evaluated result expression.

  • 源、目标和结果表达式不能包含对报表或组变量的引用。Source, destination, and result expressions cannot include references to report or group variables.

  • Lookup 不能作为以下报表项的表达式:Lookup 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.

    • Report.Language 属性。The Report.Language property.

有关详细信息,请参阅聚合函数引用(报表生成器和 SSRS)总计、聚合和内置集合的表达式作用域(报表生成器和 SSRS)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).

示例Example

在以下示例中,假定将某个表绑定到包含一个用于产品标识符 ProductID 的字段的数据集。In the following example, assume that a table is bound to a dataset that includes a field for the product identifier ProductID. 一个单独的称为“Product”的数据集包含相应的产品标识符“ID”和产品名称“名称”。A separate dataset called "Product" contains the corresponding product identifier ID and the product name Name.

在下面的表达式中, Lookup 将 ProductID 的值与名为“Product”的数据集的每行中的 ID 进行比较,当找到匹配项后,为该行返回“名称”字段的值。In the following expression, Lookup compares the value of ProductID to ID in each row of the dataset called "Product" and, when a match is found, returns the value of the Name field for that row.

=Lookup(Fields!ProductID.Value, Fields!ID.Value, Fields!Name.Value, "Product")  

另请参阅See Also

在报表中使用表达式(报表生成器和 SSRS) Expression Uses in Reports (Report Builder and SSRS)
表达式示例(报表生成器和 SSRS) Expression Examples (Report Builder and SSRS)
表达式中的数据类型(报表生成器和 SSRS) Data Types in Expressions (Report Builder and SSRS)
总计、聚合和内置集合的表达式作用域(报表生成器和 SSRS)Expression Scope for Totals, Aggregates, and Built-in Collections (Report Builder and SSRS)