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:
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
- 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 🙂