Lookup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Lookup

Lookup

Rate Topic

Display Mode

Topic Options

Author

Message

eobiki10

Posted Friday, September 28, 2012 1:37 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 3:48 PM
Points: 18, Visits: 119

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 .

Post #1366093

davdam8

Posted Wednesday, October 03, 2012 10:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:26 AM
Points: 156, Visits: 567

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

Post #1367834

eobiki10

Posted Wednesday, October 03, 2012 1:01 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 3:48 PM
Points: 18, Visits: 119

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.

Post #1367978

Daniel Bowlin

Posted Wednesday, October 03, 2012 1:16 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 2,673, Visits: 2,418

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.

Post #1367990

sanjeewan99

Posted Monday, October 08, 2012 6:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 7:23 AM
Points: 114, Visits: 386

Hi ,
Go to this link explained Look with example
http://www.bi-rootdata.com/2012/10/lookup-function-in-ssrs.html

Post #1369785

H4K

Posted Tuesday, October 09, 2012 5:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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

Post #1370317

Steven.Howes

Posted Wednesday, October 10, 2012 7:14 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 12:49 PM
Points: 370, Visits: 1,657

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

Post #1370910

eobiki10

Posted Friday, October 19, 2012 5:13 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 3:48 PM
Points: 18, Visits: 119

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

Post #1374740

sanjeewan99

Posted Friday, October 19, 2012 6:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 7:23 AM
Points: 114, Visits: 386

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

Post #1374789

« Prev Topic | Next Topic »

Permissions