Using LookupSet Function in SSRS Reports

Posted By Posted by: EPM Partners on March 5, 2013

Since any items in SSRS reports such as charts, tablix, tables etc can only be assigned to one dataset, I have found the LookupSet function quite useful to obtain data that may reside in other datasets.

For Example

DataSet1 fields:

  • ID
  • ProjectName
  • ProjectDescription
  • DateCreated

DataSet2 fields:

  • TimeByDay
  • Weather
  • Temperature

A tablix is assigned to DataSet1 showing a list of all the projects and all the fields from DataSet1 is available.

In order to get the matching ‘Temperature’ value located in DataSet2 in the tablix the LookupSet function can be used in the cells of the tablix by using the following:

=LookupSet(Fields!DateCreated.Value, Fields!TimeByDay.Value, Fields!Temparature.Value,“DataSet2″)

Notes*:

  1. First parameter is the column/field in the current dataset to match with in the other dataset
  2. Second parameter is the column/field in the other dataset that will match with the column/field specified in first parameter
  3. Third parameter is the column/field in the other dataset that will be retrieved when columns/fields specified in the first two parameters match
  4. Fourth parameter is the other datasets name as a string

Blog Posted In Blog Posted In: How to
Blog Posted In 

Leave a Reply

Your email address will not be published.