I have a report with 2 datasets. Dataset1 has SalesId1 and other fields needed and Dataset2 also has SalesId plus CostAmount which I need. On the report I need to show fields from both datasets where the salesId's are equal. I have tried using the lookup function in the expression to get the costamount but with no luck. Below is basically what I need to accomplish. Dataset1 Dataset2 Final Result SalesId1 Name ItemId SalesId CostAmount SalesId Name ItemId CostAmount 112 Bob 001 112 40.00 112 Bob 001 40.00 113 John 002 113 45.00 113 John 002 45.00

What version of SSRS are we discussing here? What do mean no luck? Please provide more details. Also, are these datasets looking at the same instance of SQL Server? If yes, can't you combine the two datasets into one.

I have a report with 2 datasets. Dataset1 has SalesId1 and other fields needed and Dataset2 also has SalesId plus CostAmount which I need. On the report I need to show fields from both datasets where the salesId's are equal. I have tried using the lookup function in the expression to get the costamount but with no luck. Below is basically what I need to accomplish. Dataset1 Dataset2 Final Result SalesId1 Name ItemId SalesId CostAmount SalesId Name ItemId CostAmount 112 Bob 001 112 40.00 112 Bob 001 40.00 113 John 002 113 45.00 113 John 002 45.00 Can you paste your lookup expression here ?Rajkumar Yelugu

Shahfaisal, I'm using SQL Server 2008 R2 Reporting Services. On the report field for the CostAmount the expression I tried is =Lookup(fields!SalesId1.value, fields!SalesId.value, fields!CostAmount.value, "Dataset2") and I receive the error 'Lookup' is invalid.InvalidIdentifier. Yes these datasets are looking at the same instance of SQL server. Not sure how to combine the two datasets into one.

The expression is =Lookup(Fields!SalesId1.Value, Fields!SalesId.Value, Fields!CostAmount.Value, "Dataset2")

Yes these datasets are looking at the same instance of SQL server. Not sure how to combine the two datasets into one. Can't you join the two queries using SalesId.

Not sure how to do that since I'm new at SSRS.

Can you post the two queries you used in the datasets?

These queries were created in Microsoft Dynamics AX 2009.