Wednesday, February 21, 2007 5:15 PM
Here's my dilema. Our organization has two db servers. One is Oracle and the other is SQL Server. Oracle contains our HR information on all employees and SQL Server contains information on various projects that we are undertaking. The HR system (Oracle) contains Employee ID's, Names, addresses, phone numbers, etc. SQL Server has information about the varous projects and Employee ID's of who is assigned to those projects. I have a report that has two data sources (one to each server) and two datasets (each populated from the two datasources). I would like to have a single table that displays the project and the name of the person who's assigned to that project. This would be achieved by linking the tables on similar information, which is the Employee ID. I've received responses in various news groups etc. saying this can be done by using subreports and query parameters but it's never explained how to go about it. Kind of like, I can tell you what you use to fly to the moon, but I can't tell you how to actually go about building the rocket that gets you there. :)
Is the functionality that I'm looking for possible in SSRS 2005??
Wednesday, February 21, 2007 6:52 PM
1. Use linked servers in SQL Server(or something similar in Oracle) to join the two datasets before RS
Create a report that has the Project dataset(which has Projectid and Employeeid in it). Add a table to the report. Add a group that group on projectid, and a details group that groups on Employee id.
Add another report that has a query that takes a employee id as a parameter. Add table or text boxes to display the information.
Add a subreport item to one cell in the details group in the first report. Point it to the second report and set the parameter for the subreport to the Employeeid of the current row.
Maybe not the best solution but it will work.
A little sloppy writing but hopefully you can figure out what i mean.
Wednesday, February 21, 2007 7:01 PM
I'm following you. So in the end, I'm not really joining the two datasets... just passing info from one as a paramter to the subreport.
That'll work perfectly! Thanks!
Wednesday, February 21, 2007 7:13 PM
Yes! Thats correct. However, if you used linked servers you could be joining them, but the passing parameters way works as well.