Possible to join results from two seperate datasets?

If I have two datasources, one pointing to Oracle and another pointing to
SQL Svr and consequently, two datasets (one from each datasource); is it
possible to create a third dataset from the two or somehow combine the two
into a single table?
Thanks,
JamesYou need to use subreports. Note that you can put a subreport into a cell of
the table control. But also note, the subreport gets executed for each row.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:%23U92zmSVHHA.1200@.TK2MSFTNGP04.phx.gbl...
> If I have two datasources, one pointing to Oracle and another pointing to
> SQL Svr and consequently, two datasets (one from each datasource); is it
> possible to create a third dataset from the two or somehow combine the two
> into a single table?
> Thanks,
> James
>|||I'm trying to figure my way through this, but it just isn't that
intuitive. Would you possibly have a simple example that you could
share?
Thanks.
--|||A subreport is a regular report with parameters. Design each report and test
independently. Then drag and drop the subreport onto the main report. Then
do a right mouse click on the subreport, properties and map the subreport
properties to a field in your dataset (you can also map to the main report
parameters, an expression, etc). I suggest trying something simple first.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:%23rozAGcVHHA.5092@.TK2MSFTNGP03.phx.gbl...
> I'm trying to figure my way through this, but it just isn't that
> intuitive. Would you possibly have a simple example that you could
> share?
> Thanks.
> --
>|||Bruce L-C [MVP] wrote:
> A subreport is a regular report with parameters. Design each report
> and test independently. Then drag and drop the subreport onto the
> main report. Then do a right mouse click on the subreport, properties
> and map the subreport properties to a field in your dataset (you can
> also map to the main report parameters, an expression, etc). I
> suggest trying something simple first.
I guess the part that's confusing me is, to design what will be the sub
report, I need to create a dataset that that report will use. I design
the report, test it and once I'm happy with it, drag it onto my main
report so that it because an actual subreport. Then, define the
parameters that it will receive from the main report, whether that be
values that are parameters that the main report receives from user
input or data from the datasets the main report uses. I don't see how
that will allow me to join/combine the two datasets that the main
report has (One populated from Oracle and the other from SQL) and
populate a single table in the subreport. Am I totally missing the
obvious'
My issue is our HR department has a user table with employee id's and
user names and other info and another department has a table that just
has employee id's but no other user info. I need to pull user info
from Oracle and Project info from SQL and join the two into a single
table based on the employee id. When I'm designing what will become
the subreport with the two datasets combined... what am I using as a
dataset at design time?
I appreciate your time and effort in helping me work through this!
--|||Yes, you are missing the point. You have one report with the Oracle dataset
and the other report has the SQL dataset. Your choice which is the main
report and which is the subreport. Design each report independently with the
appropriate parameters and test it.
Subreports are how you handle a join like this(1-many or 1-1).
OK, so in your case have the Oracle user info be the main report. Design and
test the report. Then have another report using the SQL dataset with a
parameter of employee ID. Test the second report by running it and giving it
the employee id. Then do as I mentioned before.
In this case I would suggest using the list control in the main report. A
table control in the report that will be the subreport.
This is a classic one to many, employee information and then multiple
records of the project info.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <jptyree@.duke-energy.com> wrote in message
news:OtZ$GfdVHHA.4076@.TK2MSFTNGP05.phx.gbl...
> Bruce L-C [MVP] wrote:
>> A subreport is a regular report with parameters. Design each report
>> and test independently. Then drag and drop the subreport onto the
>> main report. Then do a right mouse click on the subreport, properties
>> and map the subreport properties to a field in your dataset (you can
>> also map to the main report parameters, an expression, etc). I
>> suggest trying something simple first.
> I guess the part that's confusing me is, to design what will be the sub
> report, I need to create a dataset that that report will use. I design
> the report, test it and once I'm happy with it, drag it onto my main
> report so that it because an actual subreport. Then, define the
> parameters that it will receive from the main report, whether that be
> values that are parameters that the main report receives from user
> input or data from the datasets the main report uses. I don't see how
> that will allow me to join/combine the two datasets that the main
> report has (One populated from Oracle and the other from SQL) and
> populate a single table in the subreport. Am I totally missing the
> obvious'
> My issue is our HR department has a user table with employee id's and
> user names and other info and another department has a table that just
> has employee id's but no other user info. I need to pull user info
> from Oracle and Project info from SQL and join the two into a single
> table based on the employee id. When I'm designing what will become
> the subreport with the two datasets combined... what am I using as a
> dataset at design time?
> I appreciate your time and effort in helping me work through this!
> --
>|||Bruce L-C [MVP] wrote:
> Yes, you are missing the point. You have one report with the Oracle
> dataset and the other report has the SQL dataset. Your choice which
> is the main report and which is the subreport. Design each report
> independently with the appropriate parameters and test it.
> Subreports are how you handle a join like this(1-many or 1-1).
> OK, so in your case have the Oracle user info be the main report.
> Design and test the report. Then have another report using the SQL
> dataset with a parameter of employee ID. Test the second report by
> running it and giving it the employee id. Then do as I mentioned
> before.
> In this case I would suggest using the list control in the main
> report. A table control in the report that will be the subreport.
> This is a classic one to many, employee information and then multiple
> records of the project info.
Now it makes sense. Thanks!
--|||Its possible to use a linked server (to oracle database) to run the query
via SQL Server and reference tables in each database as part of a single
query. Users can also be granted access/mapped to oracle users.
"James" <jptyree@.duke-energy.com> wrote in message
news:eO4F5neVHHA.528@.TK2MSFTNGP03.phx.gbl...
> Bruce L-C [MVP] wrote:
>> Yes, you are missing the point. You have one report with the Oracle
>> dataset and the other report has the SQL dataset. Your choice which
>> is the main report and which is the subreport. Design each report
>> independently with the appropriate parameters and test it.
>> Subreports are how you handle a join like this(1-many or 1-1).
>> OK, so in your case have the Oracle user info be the main report.
>> Design and test the report. Then have another report using the SQL
>> dataset with a parameter of employee ID. Test the second report by
>> running it and giving it the employee id. Then do as I mentioned
>> before.
>> In this case I would suggest using the list control in the main
>> report. A table control in the report that will be the subreport.
>> This is a classic one to many, employee information and then multiple
>> records of the project info.
> Now it makes sense. Thanks!
> --
>