Archive

Posts Tagged ‘New Functions’

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

October 22, 2012 Leave a comment
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

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

October 22, 2012 Leave a comment

In SSRS 2008 R2 versions there are some functions introduced to provide extra functionality which we frequently required in BI reporting.

LookupSet is one of the functions which is used to join two dataset on similar key value having one to- many relationships and can be used to show from two different dataset fields in one tablix report.

Please follow below link in website http://msbitips.com

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

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools