Hi, I have to develop a reporting using data from 4 differents Oracle Server. Is there a way of doing this in Reporting Services 2008 R2? Thanks, Levogiro.Benedito R. Almeida Filho - BI

so you have 4 different datasets that go to 4 different Oracle servers? Yes this is very possible. Simply create an Oracle Data Source for each of your Oracle servers. Then have each dataset use the its respective data source. If you need to merge data from 4 different Oracle Servers, this too is very possible. Create the necessary database links within the Oracle Databases and you can do something like: select 'a' from dual union select 'b' from dual@server2 union select 'c' from dual@server3 union select 'd' from dual@server4

Actually, what I have to do is get to merge data from 4 different Oracle server from inside the Reporting Services. Is this possible? Thanks.Benedito R. Almeida Filho - BI

Hi Levogiro, Please check the following link: http://blog.datainspirations.com/2010/03/19/sql-server-2008-r2-reporting-services-look-up-look-down-look-all-around-part-i/ This link points to the Lookup functions, they should be the ones you should look at if you are on SSRS 2008. HTHPlease mark correct answers :)

Hi IceQB, What I really need is more like: SELECT field1, field2 FROM tables in SERVER1 UNION ALL SELECT field1, field2 FROM tables in SERVER2 UNION ALL SELECT field1, field2 FROM tables in SERVER3 UNION ALL SELECT field1, field2 FROM tables in SERVER4Benedito R. Almeida Filho - BI

Hi Levogiro, Can't you do a single SQL query using "UNION / UNION ALL" well within ORACLE and use this single query to populate a dataset in SSRS? The syntax for this still remains the same across all databases. Otherwise, do you have access to a SQL Server? If yes, like Nehemiah suggests, you could create 4 Linked Servers to your Oracles and use 4 part names to build a query that looks like your example. I do not see a way by which you could actually Union four datasets into a single one within the SSRS studio. Let me know if this helps you. Cheers. Please mark correct answers :)

if you dont have access to create the links within the databases themselves, you can use your own oracle database server and link to those 4 servers. If you must do everything within SSRS, your options are limited. Maybe your best work around doing this would be using a subreport for each server and add each subreport into a single table of master report.

I guess you are right about the RS limitations. I'll talk to my boss and try to put the query on the Oracle side. Thanks for your help. Levogiro.Benedito R. Almeida Filho - BI