SQL Oracle in 1 data foundation

We have a MS SQL 2005 database that is fed information from an Oracle database. The similar information is customer, invoice number and date. I see how to add both databases to a data foundation. What i need to do is match the information in 1 database to the other based on invoice number. I know i could do this in a subreport, but i want to know how to do this using 2 different data sources in 1 data foundation.
i would like to come into the report with the following columns
invoice1 | invoice date 1 | Invoice amt 1 | invoice2 | invoice date 2 | Invoice amt 2 |

with invoice 1 and 2 being equal unless there is a missing invoice 1 or 2 then i want to see that

Thanks in advance

Who is Participating?
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
Personally, I wouldn't take this approach, because it's inherently inefficient.  I would write a SQL View in either Oracle or SQL Server 2005 and refer to the external database within the SQL itself.  This would ensure that all of the data was pulled into a single recordset, which could then be used as the data source for the report.  If you really wanted to, you could use the SQL you would otherwise write in a view as the basis for your business view.

To access Oracle data inside a T-sql query you would need to link your Oracle server.  Here's just one example of how to do this: http://www.techrepublic.com/blog/datacenter/how-do-i-query-foreign-data-using-sql-servers-linked-servers/133

What does this have to do with Crystal Reports?

GershowitAuthor Commented:
I need to write a crystal report that will show both sets of information on 1 line. To do this I want to create a business view to aquire the information for the report.
GershowitAuthor Commented:
Figuring out how to create the input for this report was the most fun I have in a long time. Thanks for your help rhinok, you put me on the right path. Works very well and is quite fast.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.