SSRS –Join Multiple SharePoint List Columns Using LookupSet Function

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:

  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.

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:

http://msdn.microsoft.com/en-us/library/ee210576(SQL.105).aspx

Hope you liked this article  🙂

Advertisements

About Joseph Velliah
As a SharePoint Developer 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: