Post reply

Lookup

  • September 28, 2012 at 1:37 pm

    #267401

    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 .

  • October 3, 2012 at 10:17 am

    #1545048

    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

  • October 3, 2012 at 1:01 pm

    #1545167

    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.

  • October 3, 2012 at 1:16 pm

    #1545178

    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.

  • October 8, 2012 at 6:54 am

    #1546699

    Hi ,

    Go to this link explained Look with example

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

  • October 9, 2012 at 5:08 am

    #1547153

    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

  • October 10, 2012 at 7:14 am

    #1547642

    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"),",")

  • October 19, 2012 at 5:13 am

    #1550795

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

  • October 19, 2012 at 6:48 am

    #1550840

    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