Get data into report from more than one dataset.

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 2nd 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 5th 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.

Leave a Comment
  • Please add 1 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (1 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Maheshkumar S Tiwari edited Revision 1. Comment: Added tags

Page 1 of 1 (1 items)