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

Solution:

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

  • Lookup
  • LookupSet
  • MultiLookup

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

LookupSet:

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

Syntax:

Lookup(source_expression, destination_expression, result_expression, dataset)

Parameters:

source_expression

(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.

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

dataset

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

Return:

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

Example

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

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

  • 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 SSRS3

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

Reference:

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ee210576(v=sql.105)?redirectedfrom=MSDN

Hope you liked this article.