SSRS LookupSet 函数

SSRS LookupSet 函数是杂项函数之一，它将从辅助数据集中检索并返回一组值，其中存在一对多关系。例如，提取属于某个类别下的所有产品，而它们是不同的数据集。

默认情况下，Reporting Service 不允许您合并来自多个数据集的字段，但您可以使用 LookupSet 来实现。SSRS LookupSet 函数用于从不同数据集中提取匹配记录集的语法如下所示。它有四个参数：

  • 主数据集中主键列。
  • 辅助数据集中的匹配外键列。
  • 您希望从辅助数据集中显示的列名。
  • 辅助数据集的名称，用“ ”括起来。
=LookupSet(Fields!TerritoryGroupID.Value, Fields!TerritoryID.Value, Fields!StoreName.value, "Stores")

为了演示 LookupSet 函数，我们使用了 AdventureWorks2019 数据库中的 [ProductCategory] 和 [ProductSubcategory] 表。众所周知，每个产品类别有多个产品子类别，这意味着存在一对多的关系。

SSRS LookupSet 函数示例

我将添加 [ProductCategory] 表中的类别 ID 和类别名称，然后单击预览按钮以查看四个类别的记录。

我们的任务是在上表显示产品子类别名称。但是，Reporting Services 不允许您使用来自多个数据集的列。为了实现这一点，它提供了内置表达式，例如：

  1. Lookup 用于一对一关系。
  2. LookupSet 函数用于一对多关系。
  3. MultiLookup 用于多对多关系。

首先，在“类别名称”列的右侧创建一个新列。接下来，右键单击其下的文本框，然后选择“表达式”选项。为了理解报表，我建议您参考有关 SSRS 中图表、表格、分组、更多函数和格式化表格的文章。

这将打开以下表达式窗口，以从一对多关系表中获取数据。下面的 SSRS LookupSet 函数将从 [ProductSubcategory] 表返回产品子类别名称。

=LookupSet(Fields!ProductCategoryID.Value,
Fields!ProductCategoryID.Value,
Fields!Name.Value, "SubcategoryDSet")
SSRS LookupSet Function 1

从下面的报表预览中，您可以看到每行显示 #Error，因为默认情况下，上述表达式会返回多个值作为数组。我们都知道，表单元格无法容纳数组。

为了解决这种情况，我们需要一个 Join 函数将数组项组合成一个由给定分隔符分隔的字符串。请编辑 SSRS LookupSet 函数表达式并添加 Join 函数。在这里，我们使用 vbCrLf 来使每个子类别名称显示在单独的行上。如果您希望使用逗号分隔符来分隔每个子类别，请将 vbCrLf 替换为“,”。

=Join(LookupSet(Fields!ProductCategoryID.Value,
Fields!ProductCategoryID.Value,
Fields!Name.Value, "SubcategoryDSet"), vbCrLf)

我将创建另一个列，并使用 IsArray 函数来检查 LookupSet 函数是否返回了数组。

=IsArray(LookupSet(Fields!ProductCategoryID.Value,
Fields!ProductCategoryID.Value,
Fields!Name.Value, "SubcategoryDSet"))

请单击预览选项卡以查看 LookupSet 报表中的所有子类别名称。

LookupSet Function 2