SSRS –Join Multiple SharePoint List Columns Using LookupSet Function


How to create SSRS reports by joining multiple SharePoint List columns with foreign key relationship


There are three lookup functions in SQL Server 2008 R2 Reporting Services:

  1. Lookup
  2. LookupSet
  3. MultiLookup

In this article I will explain the functionality of LookupSet Function and provide a simple report to show how it is used.


Returns the set of matching values for the specified name from a dataset that contains name/value pairs.


Lookup(source_expression, destination_expression, result_expression, dataset)



(Variant) An expression that is evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!ID.Value.


(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!CustomerID.Value.


(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!PhoneNumber.Value.


A constant that specifies the name of a dataset in the report. For example, “ContactInformation”.


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


  • Create two Data Sets as shown below(here “ds_Orders” is the main dataset and “ds_Products” is the master dataset):


  • Create a report and drag and drop the necessary columns from the “ds_Orders” dataset as shown below


  • In the fourth column of this report, we have to bring the data from the “ds_Products” dataset with lookup option. To do this write the below expression on the fourth column

=Join(LookupSet(Fields!Product_Code.Value,Fields!Code.Value,Fields!Title.Value,”ds_Products”), “,”)

  • Now your Report will look something similar to this


  • That’s it you are done. Run the report you will see the below output




Hope you liked this article  🙂

About Joseph Velliah
As a SharePoint Evangelist my professional interests tend to be technical and SharePoint focused. I run a blog at "SP RIDER" where you can expect to read HOW TOs and scenarios that I run into during my day to day job. I hope my posts will give back a little to the community that is helped me.

Comments are closed.

%d bloggers like this: