Lookup

  • Hi All,

    Can someone help me with this problem? I have a table with product Id and another table with product Id and product names. I want to do a lookup on the 2nd table to return all the names of the product id. Please remember the second table contain more than 3 names of the same Product Id and I want all the names to appear. I tried the normal lookup ie Lookup(Fields!Productid.Value, Fields!Productid.Value, Fields!ProductName.Value, "Products") but it is returning just one of the ProductNames and I want it to return all the productnames .

  • Hi, it seems that we are having the same trouble.

    In my case, I want the stage name of my project, where the stage name is in DataSet1 and Project in DataSet2.

    The result I'm having is the same 'Stage Name' for all projects, wich is wrong...

    Have you found the solutions yet?

    Thanks

  • Hi,

    I do not think your problem is the same as mine. As a matter of fact, I do not think mine is possible with a lookup. I have to find another way of presenting my report without doing a Lookup ( creating another Dataset ). Yours can be done using the lookup formula I presented except if didnt get your question.

  • I can think of a few ways to do this

    SELECT ID.ProductID, Name.ProductName

    FROM Table1 AS ID

    INNER JOIN Table2 AS Name

    ON ID.ProductID = Name.ProductID

    or

    SELECT ProductID, ProductName

    FROM Table2 AS 2

    WHERE EXISTS (SELECT * FROM Table1 AS 1 WHERE 1.ProductID = 2.ProductID)

    Both should give you a recordset you can use in your report.

  • Hi ,

    Go to this link explained Look with example

    http://www.bi-rootdata.com/2012/10/lookup-function-in-ssrs.html

  • Maybe try using LookUpSet function for one-many relationship...

    BI Developer
    SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
    Please visit... ApplyBI

  • I haven't used this before but BOL(http://msdn.microsoft.com/en-us/library/ms157328.aspx) had this...

    Join(LookupSet(Fields!Productid.Value, Fields!Productid.Value, Fields!ProductName.Value, "Products"),",")

  • You guys got it. I was looking at Functions in the report model and it didnt specify to use Join in the lookupSet

  • Hello,

    You can use lookupset function of SSRS for more detail see this link

    http://www.bi-rootdata.com/2012/10/lookup-function-in-ssrs.html

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply