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*:
- First parameter is the column/field in the current dataset to match with in the other dataset
- Second parameter is the column/field in the other dataset that will match with the column/field specified in first parameter
- Third parameter is the column/field in the other dataset that will be retrieved when columns/fields specified in the first two parameters match
- Fourth parameter is the other datasets name as a string
