distributed queries

Hi all,
Do you know that can we use 'distributed queries' in reporting services to
create rdl report or use linked server as datasource.
please advise if any direction, many thanks.
e.g.
SELECT s.sales_amount
FROM access_server...region AS r,
oracle_server..sales_owner.sale AS s,
sql_server.customer_db.dbo.customer AS c
where r.region_id=s.region_id
and s.customer_id=c.customer_id
and r.region_name='Southwest'
and c.customer_name='ABC Steel'Any legal queries can be used. To do something like this you need to switch
from graphical mode to non-graphical mode. There is a button to the right of
the ... that accomplishes this. Just hover your mouse over the buttons to
find the right one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> Do you know that can we use 'distributed queries' in reporting services to
> create rdl report or use linked server as datasource.
> please advise if any direction, many thanks.
> e.g.
> SELECT s.sales_amount
> FROM access_server...region AS r,
> oracle_server..sales_owner.sale AS s,
> sql_server.customer_db.dbo.customer AS c
> where r.region_id=s.region_id
> and s.customer_id=c.customer_id
> and r.region_name='Southwest'
> and c.customer_name='ABC Steel'
>|||Hi Bruce,
I am not sure I undeestand the step(s) to generate the report. do you know
any example or document from microsoft.
Any way, thanks a lot for your help.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Any legal queries can be used. To do something like this you need to
> switch from graphical mode to non-graphical mode. There is a button to the
> right of the ... that accomplishes this. Just hover your mouse over the
> buttons to find the right one.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>|||Your problem is that RS graphical editor does not support this sort of
thing.
What you need to do is two steps. First, you cannot use the Report Wizard.
You need to create an empty report. Right mouse click on Reports, Add -> New
Item. Select Report. You get a blank report.
Second, Click on the dataset tab. Add a new dataset (drop down
combobox),Switch to non-graphical mode. Put in your SQL code.
Go to the dataset tab in the report designer, then do as I mentioned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi Bruce,
> I am not sure I undeestand the step(s) to generate the report. do you know
> any example or document from microsoft.
> Any way, thanks a lot for your help.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>|||Ho Bruce,
Thanks for your direction, i have tried it, it real works ;-)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eNLHfnyFIHA.4140@.TK2MSFTNGP03.phx.gbl...
> Your problem is that RS graphical editor does not support this sort of
> thing.
> What you need to do is two steps. First, you cannot use the Report Wizard.
> You need to create an empty report. Right mouse click on Reports, Add ->
> New Item. Select Report. You get a blank report.
> Second, Click on the dataset tab. Add a new dataset (drop down
> combobox),Switch to non-graphical mode. Put in your SQL code.
> Go to the dataset tab in the report designer, then do as I mentioned.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Hi Bruce,
>> I am not sure I undeestand the step(s) to generate the report. do you
>> know any example or document from microsoft.
>> Any way, thanks a lot for your help.
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>>
>