Problem:
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):
- 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
=Microsoft.VisualBasic.Strings.Join(LookupSet(Fields!ProductCode.Value, Fields!Code.Value, Fields!ProductTitle.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
Reference:
