Lookup function returns the first matching value for the specified name from a dataset that contains name/value pairs.
Lookup(source_expression, destination_expression, result_expression, dataset)
I am going to just create a simple Reporting Services project with a shared data source to the database.
So now let’s create a report with a couple of datasets in it. We are going to print the Sales information in one database while we would have all of our Customer information in another database. So I will create one dataset for Sales based off of the SalesLT.SalesOrderHeader table and the other one of Customer details off of the SalesLT.Customer and associated tables.
That’s where the Lookup function comes in. I will add a column on the left of my report and set the header to Company. Next I will delete my CustomerID column from the report since I won’t need that anymore. Lastly, I add a lookup function as an expression for the field under the Company column.
So in order to use the Lookup function I need to pass it four things.
Now if we run our report we can see that everything turned out exactly as it should have.
Now it should be stated that I could have used an expression for any of the values passed as parameters to the function. This allows us to do matching on things like complex keys(ie more than one column to denote a match) or bringing back complex values like the full name of the customer.