SSRS LookupSet Function

The SSRS LookupSet function is one of the Miscellaneous functions that will retrieve and return a set of values from the secondary dataset where there is a one-to-many relationship. For instance, extracting all the products belong to a category where they are both different datasets.

By default, the Reporting Service doesn’t allow you to combine fields from multiple datasets, but you can do so with the help of LookupSet. The syntax of the SSRS LookupSet function to extract the matching set of records from the different datasets is shown below. It has four arguments:

  • Primary Key column from the Primary DataSet.
  • Matching Foreign key column from the Secondary DataSet.
  • Column Name you want to show from the Secondary DataSet.
  • Name of the Secondary DataSet within the ” “.
=LookupSet(Fields!TerritoryGroupID.Value, Fields!TerritoryID.Value, Fields!StoreName.value, "Stores")

To demonstrate this SSRS LookupSet function, we use the [ProductCategory] and [ProductSubcategory] tables from the AdventureWorks2019 database. As we all know, there are multiple product subcategories for each product category, which means there are 1 to many relationships.

SSRS LookupSet function Example

Let me add the Category ID and Category Name from the [ProductCategory] table and click the preview button to see the records.

Source Report Preview

Our job is to show the Product Subcategory names in the above table. However, reporting Services does not allow you to utilize the columns from multiple datasets. To achieve the same, it provides the built-in expressions such as:

  1. Lookup for 1 to 1 relationship.
  2. SSRS LookupSet function for 1 to many relationships.
  3. MultiLookup for many to many relationships.

First, create a new column on the right side of the Category Name column. Next, right-click the textbox under it and select the Expression option. To understand the report, I suggest you refer to the articles on charts, tables, grouping, More Functions, and format tables in SSRS.

Choose the Expression option

It opens the following expression window to get data from one to many relationship tables. The SSRS LookupSet function below will return the Product Subcategory names from the [ProductSubcategory] table.

=LookupSet(Fields!ProductCategoryID.Value,
Fields!ProductCategoryID.Value,
Fields!Name.Value, "SubcategoryDSet")
SSRS LookupSet Function expression to get column from secondary dataset

From the report preview below, you can see the #Error on each row because, by default, the above expression returns multiple values as an array. As we all know, a table cell can’t hold the array.

Multiple values array error

To deal with this situation, we need a Join function to combine the array items as a string separated by the given delimiter. Please edit the SSRS LookupSet function expression and add the Join function to it. Here, we used the vbCrLf to show each subcategory name in a separate line. If you want the comma delimiter to separate each subcategory, please replace the vbCrLf with “, “.

=Join(LookupSet(Fields!ProductCategoryID.Value,
Fields!ProductCategoryID.Value,
Fields!Name.Value, "SubcategoryDSet"), vbCrLf)
SSRS LookupSet and Join Function expression to get column from secondary dataset

Let me create one more column and use the IsArray function to check whether the LookupSet function returns an array or not.

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

Please click the preview tab to see all the subcategory names in the LookupSet report.

SSRS LookupSet Function to get column from secondary dataset