Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSRS Parameters with 2 datasets

Answered SSRS Parameters with 2 datasets

  • Monday, May 21, 2012 7:40 PM
     
      Has Code
    Sign In to Vote
    0

    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?

     

All Replies

  • Monday, May 21, 2012 8:22 PM
    Moderator
     
     
    Sign In to Vote
    1

    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.

     
  • Monday, May 21, 2012 9:42 PM
     
     
    Sign In to Vote
    0
    cool, Thank syed

    Akki

     
  • Tuesday, May 22, 2012 11:44 AM
     
      Has Code
    Sign In to Vote
    0

    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


    • Edited by westerncj Tuesday, May 22, 2012 11:45 AM
    •  
     
  • Monday, May 28, 2012 1:39 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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

    1. 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)
    2. 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)
    3. 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.