I'm using this query in my report. I need a field called OnHand balance to show up on my report but it's in a different dataset. USE TSSWork SELECT h.Pallet_Part_Type , o.item , i.Description , o.Allocated , SUM(d.Quantity) AS ScrapQuantity , b.ExteriorColor , b.FinishingCharacteristic , b.Style , b.Species FROM dbo.SalvageVoicePalletHeader h JOIN dbo.SalvageVoicePalletDetail d ON h.Header_Index = d.Detail_Index JOIN mrploc..OrderAlloc o ON d.Parent_Item = o.Item JOIN mrploc..item i ON i.item = o.item JOIN mrploc..ItemBase b ON LTRIM(SUBSTRING(i.item, 3, 10)) = b.ItemBase WHERE EXISTS ( SELECT * FROM mrploc..OrderAlloc WHERE d.Parent_Item = o.Item ) AND h.Pallet_Part_Type = 'Door' AND h.Pallet_Type = 'SCRAP' AND o.Department = 'PAINT' AND o.Allocated > i.OnHand AND CONVERT(CHAR(8), d.Date_Time, 1) = CONVERT(CHAR(8), GETDATE(), 1) AND CONVERT(CHAR(8), o.RequiredOn, 1) = CONVERT(CHAR(8), GETDATE(), 1) GROUP BY h.Pallet_Part_Type , o.item , i.item , i.Description , o.Allocated , b.ExteriorColor , b.FinishingCharacteristic , b.Style , b.Species ORDER BY o.item I know how to add another dataset but how do I get the parameters to work? I'm using this select statment for dataset1 USE MRPHist SELECT onhand FROM dbo.ItemBalance WHERE item = @item I was trying to link on the item field. I'm not sure if i'm doing this correctly. I just want the OnHand balance to show up for every row on my main report but it's in another dataset. How do I show it based on the main dataset's item number?

Hi There If you are using SSRS 2008 you can use lookup function. You can only use when there is one-to-one relationship between source and destination. if there is 1 to many relationship you need to use LookupSet instead. The syntax for lookup function is look like this Lookup(Fields! item.Value, Fields! item.Value,Fields!onhand.Value,"Dataset2") Where the first Fields! item.Value is your first dataset field ,("Dataset1")and Fields! item.Value is the field in the second dataset ("Dataset2")and both have one to one relationship with each other Fields! onhand.Value is the value which you are getting from second dataset If you have any question please let me know. Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

cool, Thank syedAkki

Sadly I'm using ssrs 2005... Is there any other way?? This is in essence what I'm trying to do.. I'm trying to pull the onhand from another server (MRPHist).. mrphist..onhand = (SELECT onhand from mrphist..ItemBalance WHERE Item = o.item) I know this isn't correct because you don't put the server before the field (onhand).. I want to pass o.item from the other server/database in to get the values for the onhand column....This doesn't seem possible. I have two different datasets.. Is there anyway to pull from the other dataset in the main select statement? USE TSSWork SELECT h.Pallet_Part_Type, mrphist..onhand = (SELECT onhand from mrphist..ItemBalance WHERE Item = o.item) , o.item , i.Description , i.OnHand , o.Allocated , SUM(d.Quantity) AS ScrapQuantity , b.ExteriorColor , b.FinishingCharacteristic , b.Style , b.Species FROM dbo.SalvageVoicePalletHeader h JOIN dbo.SalvageVoicePalletDetail d ON h.Header_Index = d.Detail_Index JOIN mrploc..OrderAlloc o ON d.Parent_Item = o.Item JOIN mrploc..item i ON i.item = o.item JOIN mrploc..ItemBase b ON LTRIM(Substring(i.item, 3, 10)) = b.ItemBase WHERE onHand in (@onhand) AND EXISTS ( SELECT * FROM mrploc..OrderAlloc WHERE d.Parent_Item = o.Item ) AND h.Pallet_Part_Type = @PalletPartType AND b.ExteriorColor = @Color AND h.Pallet_Type = 'SCRAP' AND o.Department = 'PAINT' AND CONVERT(CHAR(8), d.Date_Time, 1) = CONVERT(CHAR(8), GETDATE(), 1) AND CONVERT(CHAR(8), o.RequiredOn, 1) = CONVERT(CHAR(8), GETDATE(), 1) GROUP BY h.Pallet_Part_Type , o.item , i.item , i.Description , i.OnHand , o.Allocated , b.ExteriorColor , b.FinishingCharacteristic , b.Style , b.Species ORDER BY o.item

Hi There In that case you can use temporary table approach inside your script which is very common in these types of scenarios so please follow these steps then If you are using stored procedure then you can insert all your values coming from your main query to the temporary table but please create another column for onhand quantity which is initially 0(you can do similar thing inside your query if you are not using stored procedures)After inserting the main records you can bring the onhand quantity from another server in the same Stored procedure/query or you can use a wrapper stored procedure inside you main stored procedure(depends on your preference)Update your main temporary table with the on hand quantity bring in the second query based on your items I hope this will help and resolve your problem Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Hi There In that case you can use temporary table approach inside your script which is very common in these types of scenarios so please follow these steps then If you are using stored procedure then you can insert all your values coming from your main query to the temporary table but please create another column for onhand quantity which is initially 0(you can do similar thing inside your query if you are not using stored procedures)After inserting the main records you can bring the onhand quantity from another server in the same Stored procedure/query or you can use a wrapper stored procedure inside you main stored procedure(depends on your preference)Update your main temporary table with the on hand quantity bring in the second query based on your items I hope this will help and resolve your problem Many Thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.