Home > SSRS > SSRS Tablix Report having Data from Multiple Datasets – Lookup Function SSRS 2008 R2

SSRS Tablix Report having Data from Multiple Datasets – Lookup Function SSRS 2008 R2

In SSRS 2008 R2 versions there are some functions introduced to provide extra functionality which we frequently required in BI reporting.
Lookup is one of the functions which is used to join two dataset on similar key value and can be used to show from two different dataset fields in one tablix report.
In one tablix we can use only one dataset , so If need to have one column value from other dataset we can use lookup Function,
Let see how to use the lookup function in SSRS reports.
 If we have one Dataset  resulting Product data and second Dataset resulting Product Subcategory data.
But in one table we want data like ProductSubcategoryName and ProductName.
If we execute the below statement as using joins we can get the result in one single query and it can be used in one dataset. But we want to test Lookup function.

DISTINCT PS.Name ProductSubcatName,P.Name ProductName from Production.Product PINNER

JOIN Production.ProductSubcategory PSON


 Above query result as shown in below screenshot:-
Same result we want in SSRS report without using single query or single dataset, we will use multiple Datasets.
 We have created two dataset , one for Product and another for ProductSubCategory as shown in beow two screenshots.
lets create one tablix control in report designer. Assaign the Product_DS dataset to this tablix and Drag ProductName Field into the First Column.
Report Design Look like below screenhot:-
Now rightclick on the second column data area to get the expression editor as shown in below screen:-
In Expression Editor go to Category–> CommonFunctions –> Miscellaneous –> lookup . You can see in below screen where I am pointing.

Write the expression as given below:-=Lookup(Fields!ProductSubcategoryID.Value,Fields!ProductSubcategoryID.Value,Fields!ProductSubcatName.Value,“ProductSubcategory_DS”)

The above expression understanding is given below screen:-
Clik ok and Preview the report.
By seeing the preview of the report and sample query result, its matched.
Hence we can use lookup function to join to datasets in one tablix control.


Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: