Linking datasets

Question

Linking datasets

mick L

SSCommitted

Points: 1526
More actions
May 13, 2005 at 6:17 am

#166769

Folks,
I need to pull data from both Oracle and SQL Server and join the two to create a report. Can I join two datasets in Reporting Services?
Cheers
Mick

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

You can use them in seperate datasets in the report, but RS can't (won't?) merge them. But you can if you use distributed queries in MSSQL (ie use a linked server) but again, this isn't RS doing the merge.

Steve.

mick L SSCommitted Points: 1526 More actions · Answer 2

Thanks Steve,

The oracle folks at my company would not allow a linked server from "mere" SQL Server. Can I use a web service as a datasource in Reporting Services? or is there a recommended method of consuming a dataset created outside of Reporting Services?

Any links would be much appreciated.

Thanks

Mick

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Short answer is yes, the longer one is that you'll need to do a little work to consume the Web Service. One of the great things about RS is it's extensability - what you'll need to do is create a Data Processing Exension.

Keep in mind that although it's not hard to write one (the BOL gives reasonable examles and i think there's a sample one for text files), a simpler solution could be to convince the oracle guys to do a distributed query from their oracle DB to the MSSQL (rather than SQL to oracle, which is obviously 'not worthy' ).

If you want to go down the line of writing one, look up 'Implementing a Data Processing Extension' in the RS BOL.

Cheers,

Steve.

mick L SSCommitted Points: 1526 More actions · Answer 4

Thanks Steve,

I'll report back on how it works out for me in case anyone is interested.

Cheers

Mick

Jerome BERTHAUD Newbie Points: 1 More actions · Answer 5

Hello

An other way to do that is to use sub report. In this case you create a parameterized report to access data to oracle and you place this sub report in detail section of your report.

It works fine for few records due to number of reports executed (n + 1 rows in your main report)

It should avoid to you to create a date extension.

HTH