Get data into report from more than one dataset.

Get data into report from more than one dataset.

Summary: This is in an example of using reporting services for creating a report by using two dataset. Remember this for doing this we should have at least one match column in both data set.

Solution:

I am selecting two tables from AdventureWorks database.

SELECT * FROM [AdventureWorks].[Production].[ProductCategory]

SELECT * FROM [AdventureWorks].[Production].[ProductSubcategory]

Here I have one match column ProductCategoryID in both tables.

Go to report and create data source and two dataset.

Here I created dataset1 and now creating 2^nd dataset.

So now I have 2 dataset here in report.

Here I am taking category name in new column, clicking right click on textbox, go to value then write expression. I am using lookup.

Lookup (

Matched column value from first data source,

Matched column value from second data source,

The value of column which you want to put from second data source,

”second dataset name”)

Here I am putting value in 5^th column as category modified date, also applying same lookup function for next column.

Report is ready.

Click on preview you can see here column from both data set.

Here product subcategory id, subcategory name, subcategory modified date fields are from dataset2 and category name and category modified date fields are from dataset1.