how to combine the data as one report (rdl) from accessing two data sources (ODBC)

Hi,
Does any one know how to combine the data as one report (rdl) from accessing
two data sources (ODBC) without using integration services?
any extend method? as I know one rdl should be able to connect to one data
source only. pleaae help & advise
For example:
SQL2005:
source 1: mssql 2000: employee table: employee id, employee name
source 2: Oracle: employee address table: employee id, employee address
I want join get the data from two sources such that a rdl report shows
(employee id, employee name, employee address). Of-course my real case is
quite complexOn Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi,
> Does any one know how to combine the data as one report (rdl) from accessing
> two data sources (ODBC) without using integration services?
> any extend method? as I know one rdl should be able to connect to one data
> source only. pleaae help & advise
> For example:
> SQL2005:
> source 1: mssql 2000: employee table: employee id, employee name
> source 2: Oracle: employee address table: employee id, employee address
> I want join get the data from two sources such that a rdl report shows
> (employee id, employee name, employee address). Of-course my real case is
> quite complex
I'm not sure if this will help, but you might try using a subreport
for a different datasource. That way a secondary report can have it's
own datasource and it can be included in the main report by way of a
subreport control. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique
But can I use source 1's employee id list as pararmeter for source 2?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case is
>> quite complex
>
> I'm not sure if this will help, but you might try using a subreport
> for a different datasource. That way a secondary report can have it's
> own datasource and it can be included in the main report by way of a
> subreport control. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||First, just to clear up a misconception you have. A report can have multiple
datasources. What it cannot do is join those data sources. For instance,
let's say you have a parameter on a report. One data source returns a single
record. You could put that in a table or list or whatever. Next below that
you want a bunch of detail. For that you have a second dataset to a
different datasource. No problem whatsoever. However if you want master
detail of the sense that you have multiple masters and for each master
record you want to show detail then that cannot be done with a single
report. You have to use subreports. Master-detail reports are exactly for
what subreports are designed.
You need to understand two things about subreports. First, subreports are
just a report. Like all reports you can have report parameters (and it would
be a rare subreport that didn't). Design and test your subreport stand alone
and make sure it works.
The second thing to understand is the act of embedding and hooking up the
subreport. I just drag and drop the report previously created and tested
onto my main report. You can drag it onto a list. You can drag it onto a
cell of a table control. I do both. Then right mouse click onto the
subreport and map your parameters. You can map your parameters to a field of
a dataset, you can map them to a report parameter on the main report, you
can map them to an expression.
Subreports are very important in RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> Thanks Enrique
> But can I use source 1's employee id list as pararmeter for source 2?
>
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case
>> is
>> quite complex
>>
>> I'm not sure if this will help, but you might try using a subreport
>> for a different datasource. That way a secondary report can have it's
>> own datasource and it can be included in the main report by way of a
>> subreport control. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||Hi Bruce,
Thanks. it becomes clear that i think I am able to do & try. You are very
cool. Thanks a lot.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> First, just to clear up a misconception you have. A report can have
> multiple datasources. What it cannot do is join those data sources. For
> instance, let's say you have a parameter on a report. One data source
> returns a single record. You could put that in a table or list or
> whatever. Next below that you want a bunch of detail. For that you have a
> second dataset to a different datasource. No problem whatsoever. However
> if you want master detail of the sense that you have multiple masters and
> for each master record you want to show detail then that cannot be done
> with a single report. You have to use subreports. Master-detail reports
> are exactly for what subreports are designed.
> You need to understand two things about subreports. First, subreports are
> just a report. Like all reports you can have report parameters (and it
> would be a rare subreport that didn't). Design and test your subreport
> stand alone and make sure it works.
> The second thing to understand is the act of embedding and hooking up the
> subreport. I just drag and drop the report previously created and tested
> onto my main report. You can drag it onto a list. You can drag it onto a
> cell of a table control. I do both. Then right mouse click onto the
> subreport and map your parameters. You can map your parameters to a field
> of a dataset, you can map them to a report parameter on the main report,
> you can map them to an expression.
> Subreports are very important in RS.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
>> Thanks Enrique
>> But can I use source 1's employee id list as pararmeter for source 2?
>>
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi,
>> Does any one know how to combine the data as one report (rdl) from
>> accessing
>> two data sources (ODBC) without using integration services?
>> any extend method? as I know one rdl should be able to connect to one
>> data
>> source only. pleaae help & advise
>> For example:
>> SQL2005:
>> source 1: mssql 2000: employee table: employee id, employee name
>> source 2: Oracle: employee address table: employee id, employee
>> address
>> I want join get the data from two sources such that a rdl report shows
>> (employee id, employee name, employee address). Of-course my real case
>> is
>> quite complex
>>
>> I'm not sure if this will help, but you might try using a subreport
>> for a different datasource. That way a secondary report can have it's
>> own datasource and it can be included in the main report by way of a
>> subreport control. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>
>|||On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi Bruce,
> Thanks. it becomes clear that i think I am able to do & try. You are very
> cool. Thanks a lot.
> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> > First, just to clear up a misconception you have. A report can have
> > multiple datasources. What it cannot do is join those data sources. For
> > instance, let's say you have a parameter on a report. One data source
> > returns a single record. You could put that in a table or list or
> > whatever. Next below that you want a bunch of detail. For that you have a
> > second dataset to a different datasource. No problem whatsoever. However
> > if you want master detail of the sense that you have multiple masters and
> > for each master record you want to show detail then that cannot be done
> > with a single report. You have to use subreports. Master-detail reports
> > are exactly for what subreports are designed.
> > You need to understand two things about subreports. First, subreports are
> > just a report. Like all reports you can have report parameters (and it
> > would be a rare subreport that didn't). Design and test your subreport
> > stand alone and make sure it works.
> > The second thing to understand is the act of embedding and hooking up the
> > subreport. I just drag and drop the report previously created and tested
> > onto my main report. You can drag it onto a list. You can drag it onto a
> > cell of a table control. I do both. Then right mouse click onto the
> > subreport and map your parameters. You can map your parameters to a field
> > of a dataset, you can map them to a report parameter on the main report,
> > you can map them to an expression.
> > Subreports are very important in RS.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> >> Thanks Enrique
> >> But can I use source 1's employee id list as pararmeter for source 2?
> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> Hi,
> >> Does any one know how to combine the data as one report (rdl) from
> >> accessing
> >> two data sources (ODBC) without using integration services?
> >> any extend method? as I know one rdl should be able to connect to one
> >> data
> >> source only. pleaae help & advise
> >> For example:
> >> SQL2005:
> >> source 1: mssql 2000: employee table: employee id, employee name
> >> source 2: Oracle: employee address table: employee id, employee
> >> address
> >> I want join get the data from two sources such that a rdl report shows
> >> (employee id, employee name, employee address). Of-course my real case
> >> is
> >> quite complex
> >> I'm not sure if this will help, but you might try using a subreport
> >> for a different datasource. That way a secondary report can have it's
> >> own datasource and it can be included in the main report by way of a
> >> subreport control. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant
You're welcome. Also, one thing that Bruce did not mention. If you
want to reference a single value from one dataset (i.e., ds1) in
another (i.e., ds2) you can reference it via an aggregate expression
similar to the following:
=Max(Fields!employee_id.Value, "ds1")
Also, you could try having one of the datasets used as a hidden multi-
select parameter and then reference it from the other dataset via an
expression:
=Parameters!employee_id.Value
Hope this helps further clarify things for you.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique,
Thanks for your suggestion, I will try
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191980383.493910.75050@.g4g2000hsf.googlegroups.com...
> On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> Hi Bruce,
>> Thanks. it becomes clear that i think I am able to do & try. You are very
>> cool. Thanks a lot.
>> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in
>> messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
>> > First, just to clear up a misconception you have. A report can have
>> > multiple datasources. What it cannot do is join those data sources. For
>> > instance, let's say you have a parameter on a report. One data source
>> > returns a single record. You could put that in a table or list or
>> > whatever. Next below that you want a bunch of detail. For that you have
>> > a
>> > second dataset to a different datasource. No problem whatsoever.
>> > However
>> > if you want master detail of the sense that you have multiple masters
>> > and
>> > for each master record you want to show detail then that cannot be done
>> > with a single report. You have to use subreports. Master-detail reports
>> > are exactly for what subreports are designed.
>> > You need to understand two things about subreports. First, subreports
>> > are
>> > just a report. Like all reports you can have report parameters (and it
>> > would be a rare subreport that didn't). Design and test your subreport
>> > stand alone and make sure it works.
>> > The second thing to understand is the act of embedding and hooking up
>> > the
>> > subreport. I just drag and drop the report previously created and
>> > tested
>> > onto my main report. You can drag it onto a list. You can drag it onto
>> > a
>> > cell of a table control. I do both. Then right mouse click onto the
>> > subreport and map your parameters. You can map your parameters to a
>> > field
>> > of a dataset, you can map them to a report parameter on the main
>> > report,
>> > you can map them to an expression.
>> > Subreports are very important in RS.
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
>> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
>> >> Thanks Enrique
>> >> But can I use source 1's employee id list as pararmeter for source 2?
>> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
>> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
>> >> Hi,
>> >> Does any one know how to combine the data as one report (rdl) from
>> >> accessing
>> >> two data sources (ODBC) without using integration services?
>> >> any extend method? as I know one rdl should be able to connect to
>> >> one
>> >> data
>> >> source only. pleaae help & advise
>> >> For example:
>> >> SQL2005:
>> >> source 1: mssql 2000: employee table: employee id, employee name
>> >> source 2: Oracle: employee address table: employee id, employee
>> >> address
>> >> I want join get the data from two sources such that a rdl report
>> >> shows
>> >> (employee id, employee name, employee address). Of-course my real
>> >> case
>> >> is
>> >> quite complex
>> >> I'm not sure if this will help, but you might try using a subreport
>> >> for a different datasource. That way a secondary report can have it's
>> >> own datasource and it can be included in the main report by way of a
>> >> subreport control. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>
> You're welcome. Also, one thing that Bruce did not mention. If you
> want to reference a single value from one dataset (i.e., ds1) in
> another (i.e., ds2) you can reference it via an aggregate expression
> similar to the following:
> =Max(Fields!employee_id.Value, "ds1")
> Also, you could try having one of the datasets used as a hidden multi-
> select parameter and then reference it from the other dataset via an
> expression:
> =Parameters!employee_id.Value
> Hope this helps further clarify things for you.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Oct 10, 1:37 am, "frankie lo" <frankiec...@.hotmail.com> wrote:
> Hi Enrique,
> Thanks for your suggestion, I will try
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1191980383.493910.75050@.g4g2000hsf.googlegroups.com...
> > On Oct 9, 7:37 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> Hi Bruce,
> >> Thanks. it becomes clear that i think I am able to do & try. You are very
> >> cool. Thanks a lot.
> >> "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com> wrote in
> >> messagenews:us0p%23inCIHA.3884@.TK2MSFTNGP05.phx.gbl...
> >> > First, just to clear up a misconception you have. A report can have
> >> > multiple datasources. What it cannot do is join those data sources. For
> >> > instance, let's say you have a parameter on a report. One data source
> >> > returns a single record. You could put that in a table or list or
> >> > whatever. Next below that you want a bunch of detail. For that you have
> >> > a
> >> > second dataset to a different datasource. No problem whatsoever.
> >> > However
> >> > if you want master detail of the sense that you have multiple masters
> >> > and
> >> > for each master record you want to show detail then that cannot be done
> >> > with a single report. You have to use subreports. Master-detail reports
> >> > are exactly for what subreports are designed.
> >> > You need to understand two things about subreports. First, subreports
> >> > are
> >> > just a report. Like all reports you can have report parameters (and it
> >> > would be a rare subreport that didn't). Design and test your subreport
> >> > stand alone and make sure it works.
> >> > The second thing to understand is the act of embedding and hooking up
> >> > the
> >> > subreport. I just drag and drop the report previously created and
> >> > tested
> >> > onto my main report. You can drag it onto a list. You can drag it onto
> >> > a
> >> > cell of a table control. I do both. Then right mouse click onto the
> >> > subreport and map your parameters. You can map your parameters to a
> >> > field
> >> > of a dataset, you can map them to a report parameter on the main
> >> > report,
> >> > you can map them to an expression.
> >> > Subreports are very important in RS.
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> > "frankie lo" <frankiec...@.hotmail.com> wrote in message
> >> >news:OVq07ohCIHA.464@.TK2MSFTNGP02.phx.gbl...
> >> >> Thanks Enrique
> >> >> But can I use source 1's employee id list as pararmeter for source 2?
> >> >> "EMartinez" <emartinez...@.gmail.com> wrote in message
> >> >>news:1191892063.275037.327290@.g4g2000hsf.googlegroups.com...
> >> >> On Oct 8, 7:53 pm, "frankie lo" <frankiec...@.hotmail.com> wrote:
> >> >> Hi,
> >> >> Does any one know how to combine the data as one report (rdl) from
> >> >> accessing
> >> >> two data sources (ODBC) without using integration services?
> >> >> any extend method? as I know one rdl should be able to connect to
> >> >> one
> >> >> data
> >> >> source only. pleaae help & advise
> >> >> For example:
> >> >> SQL2005:
> >> >> source 1: mssql 2000: employee table: employee id, employee name
> >> >> source 2: Oracle: employee address table: employee id, employee
> >> >> address
> >> >> I want join get the data from two sources such that a rdl report
> >> >> shows
> >> >> (employee id, employee name, employee address). Of-course my real
> >> >> case
> >> >> is
> >> >> quite complex
> >> >> I'm not sure if this will help, but you might try using a subreport
> >> >> for a different datasource. That way a secondary report can have it's
> >> >> own datasource and it can be included in the main report by way of a
> >> >> subreport control. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant
> > You're welcome. Also, one thing that Bruce did not mention. If you
> > want to reference a single value from one dataset (i.e., ds1) in
> > another (i.e., ds2) you can reference it via an aggregate expression
> > similar to the following:
> > =Max(Fields!employee_id.Value, "ds1")
> > Also, you could try having one of the datasets used as a hidden multi-
> > select parameter and then reference it from the other dataset via an
> > expression:
> > =Parameters!employee_id.Value
> > Hope this helps further clarify things for you.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant