Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Lookup Expand / Collapse
Author
Message
Posted Friday, September 28, 2012 1:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:51 AM
Points: 80, Visits: 332
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
Posted Wednesday, October 3, 2012 10:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668

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
Posted Wednesday, October 3, 2012 1:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:51 AM
Points: 80, Visits: 332
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
Posted Wednesday, October 3, 2012 1:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:41 AM
Points: 2,818, Visits: 2,553
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
Posted Monday, October 8, 2012 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 19, 2013 10:40 PM
Points: 116, Visits: 395
Hi ,
Go to this link explained Look with example
http://www.bi-rootdata.com/2012/10/lookup-function-in-ssrs.html
Post #1369785
Posted Tuesday, October 9, 2012 5:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
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
Posted Wednesday, October 10, 2012 7:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 7, 2014 2:30 PM
Points: 579, Visits: 2,086
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
Posted Friday, October 19, 2012 5:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:51 AM
Points: 80, Visits: 332
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
Posted Friday, October 19, 2012 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 19, 2013 10:40 PM
Points: 116, Visits: 395
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 »

Add to briefcase

Permissions Expand / Collapse