When SQL Server 2008 R2 was released several new Reporting Services expressions were made available that would help join multiple datasets together. Those functions were the Lookup Function, LookupSet Function and the MultiLookup Function. My general recommendation for these function is not to use them when you have two dataset from the same data source. They’re really intended for connecting datasets together that are not directly related because otherwise I could just handle it in my source query.
My experience with them had been fairly academic until recently when I had a real scenario where the Lookup Function would be beneficial. I was working with a customer that had a report requirement that needed data from DB2 and Oracle in a single tabular report. What made this circumstance more difficult was that the join between the DB2 dataset and the Oracle dataset was not a single column but two.
Seems basic enough right? If we were writing T-SQL we would simply do a join on multiple columns with an AND between them. Because these are two different data sources in Reporting Services I had to get a little more creative. If you’ve used the Lookup function in SSRS (template below) expressions then you know it can only accept one field from the source and one from the destination. So what if you have two columns to join on?
Lookup(source_expression, destination_expression, result_expression, dataset)
The approach I took was to concatenate the two join columns together and then place the concatenated value in the Lookup function. I also placed a literal string between the two columns to protect myself from coincidental matches. To make the solution a little cleaner I created a calculated field on each dataset that handled the expression. So both datasets had a calculated field I called JoinColumn and was defined similar to this:
=Fields!JoinColumn1.Value + ”||” + Fields!JoinColumn2.Value
Notice the double pipes that are used here to protect from coincidental matches. With these calculated field created on each dataset now my Lookup function would be fairly standard. Here’s my end result:
Lookup(Fields!JoinColumn.Value, Fields!JoinColumn.Value, Fields!ReturnColumn.Value, “DataSet2”)
I hope this helps as you find practical uses for the Lookup Function in Reporting Services.
Devin Knight 
Great solution and just the fix I was looking for to join an SSAS dataset with a SQL reference dataset. Thanks!
In Oracle the syntax is:
=Fields!JoinColumn1.Value || ”||” || Fields!JoinColumn2.Value
I tried your solution and it works great when the Join columns are of the same data type, both strings for example… When i tried to create the new field by a string column and a numeric column i got an #Error even when i converted the numeric field in character with the CChar() function… 😦
Any suggestions?
Try using CSTR()
Works great!!!
Thank you very much Devin!
I will follow your blog from now ant then… 🙂
Thanks Devin, I appreciate the solution. I spent 4 hours on this today trying different ways to do a multiple join with a lookupset. This worked perfectly, and both of my data sources were SharePoint lists, so T-SQL wasn’t an option. Have a good weekend.
I tried using your concatenated field solution, but it is still not returning any data. Does it matter what the literal string is? Does it need to be a double pipe?
Yes if you’re joining multiple columns you may have to convert some of the data types to be strings. You don’t have to use double pipes though that was just my way to ensure I don’t have coincidental matches on my join
Hi Devin,
Your idea really helped me out. Thanks for the solution. While technically there is a multi-join (MultiLookup) in 2008 R2, your solution is simple and it works. Thanks! Don
Thanks alot great tip
Thanks Devon, I just started using the lookup function after seeing your presenation in Minneapolis recently; and this just adds more functionality!
Really simple yet very sophisticated solution to my problem! Thank you so much!
Very simple and clever solution! Worked like magic. Thanks and much appreciated.
SSRS – LookUp, MultiLookUp and LookupSet Functions
These beautiful features bring a lot of satisfaction to report writer and developers to marge values from two datasets. These functions are is playing a very handy role to combine data from two datasets in a single data region which was not possible in earlier versions of Reporting Services.
SSRS – LookUp, MultiLookUp and LookupSet Functions
I have three datasets with the same column name in all three datasets. I need to have the data of the dimension column from all three datasets. What function can I use to get the data from all three datasets
Are they coming from different data sources? If not can they just be brought together in the source query?