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.
Select

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

JOIN Production.ProductSubcategory PSON

P.ProductSubcategoryID=PS.ProductSubcategoryID

 Above query result as shown in below screenshot:-
SampleCaseQueryResult
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.
Product_Dataset
ProductSubcategory_Dataset
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:-
Report_with_ProductOnly
Now rightclick on the second column data area to get the expression editor as shown in below screen:-
SecondColumnExpression
In Expression Editor go to Category–> CommonFunctions –> Miscellaneous –> lookup . You can see in below screen where I am pointing.
ExpressionEditor_LookUpFunction

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:-
ExpressionEditor_LookUpFunctionUse
Clik ok and Preview the report.
ReportPreview
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.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
  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: