Tuesday, August 23, 2011

Lookup function in SQL Server 2008 Reporting Services


These days I'm exploring SQL Server 2008 R2 Reporting Services and one thing where i stuck is to combine two dataset fields in one table. After some research i found a very nice way of doing it using Lookup Function. Lookup function is a new feature in SQL Server 2008 R2 Reporting Services.

Syntax
=Lookup(Fields!CustomerId.Value,Fields!CustomerId.Value,Fields!TotalOrders.Value,"DataSetB")

Parameters Description
Parameter 1: Field of first dataset to be matched as criteria
Parameter 2: Field of second dataset to be matched as criteria
Parameter 3: Field to show in that column where parameter1 and parameter2 matches.
Parameter 4: Name of dataset from where you want to select new field

Example
Ill try to explain it by an example. We have two datasets having customer data, DatasetA have customer personal details and DatasetB have TotalOrders that customer have. Here is the schema of our Datasets.


















In my Report DataSetA is what the default table showing customer details and now i just want a new column in my report for TotalOrders for every customer. If i try to drag TotalOrders column in this table it would not let me do. To do so now ill add a new column in that table then right click on that field i have just added and select expression. Write a new expression like this

=Lookup(Fields!CustomerId.Value,Fields!CustomerId.Value,Fields!TotalOrders.Value,"DataSetB")

and click ok. It will show TotalOrders for every customer that matches CustomerId column in both Datasets. The resultant Dataset will look like this