SQL Server Reporting Services 2008 R2: Lookup Fields!

Well, I promised the XRM Virtual Users Group ( website | twitter ) and Julie Yack ( blog | twitter ) that I would provide them a blog of an example of using the Lookup feature in SQL Server Reporting Services 2008 R2….well, that time has come. I will throw out the caveat that I have been ill all day and am now under the heavy influence of, my friend, Mr. Nyquil. So if the post seems kind of disjointed then you will understand why.

So to start out with I am going to just create a simple Reporting Services project with a shared data source to the AdventureWorksLT database. Nothing fancy here.

However, please note that I could have 2 separate data sources pointing to 2 totally different databases. I am just trying to keep things simple here for demonstration purposes.

So now let’s create a report with a couple of datasets in it. We are going to pretend that we have 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. Nothing crazy going on here.

So now the problem is that since the data for the customers is in a totally separate dataset …How do we relate the two in order to remove CustomerID from our table and replace it with say the customer’s company name? 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. This is where it gets kind of tricky….

=Lookup(Fields!CustomerID.Value,Fields!CustomerID.Value,
Fields!CompanyName.Value,"Customers")

So in order to use the Lookup function I need to pass it four things.

1. The value from the original dataset that is going to be used as the left side of the match.
2. The value from the second dataset the is going to be the right side of the match. This is the tricky part because when you look at the two parameters they look to be the same. You just have to  remember that the field references are named the same in each dataset ..SSRS knows which one it is supposed to be looking in.
3. The value from the second dataset that needs to be pulled out.
4. The name of the second dataset where all this lookee upee stuff is supposed to be going on.

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.

It should also be noted that there are  two other Lookup functions: LookupSet and MultiLookup that return arrays of matching values. I will try to detail those in a follow up blog post.

I am including the sample project below to help you out .

Sample Reports

Cheers!
Aj