SQL Server Performance

Using 2 datasets in one report table ?

Discussion in 'SQL Server Reporting Services' started by dougwood, Feb 10, 2004.

  1. dougwood New Member

    I am trying to combine 2 tables from different databases into 1 report. (Can't do a join).
    SO right now I have the data in 2 separate datasets.
    There isn't a key that relates the two datasets, only 'Date' fields. IE: I am joining the data that occurs within each month between the 2 datasets.

    I have not had any luck grouping this data together, as I am always getting the error "Report item expression can only occur within the current data set scope..."

    BOL doesn't seem to cover this area, so I'm looking for any help/documentation on this, or if this is even possible.

    Thanks.
  2. jasper_smith New Member

    I don't believe this is possible, is there any reason you can't join on the source system ?

    HTH

    Jasper Smith
  3. dougwood New Member

    No, the tables are coming from 2 completely different Oracle DB's on different servers. This leads me to my next question. Let's say I get the data the way I want in a regular ASP.NET web form by populating a DataSet with the two tables and and joining the data in a 3rd table. Can I access this dataset from Reporting Services somehow ? I think this is possible although I haven't seen any examples/explanations of how to do it??

    thanks
  4. jasper_smith New Member

  5. unclebiguns New Member

    You could do a subreport with a link on the date field. I have not tried this, but it might work.

    Since you have SQL Server you could setup Linked Servers to your 2 oracle instances and then write an sp that combines the data in SQL Server or just a query in RS that would join the data.

    Jack Corbett, DBA
    Nexfor Fraser Papers
  6. knarf New Member

    quote:Originally posted by dougwood

    No, the tables are coming from 2 completely different Oracle DB's on different servers. This leads me to my next question. Let's say I get the data the way I want in a regular ASP.NET web form by populating a DataSet with the two tables and and joining the data in a 3rd table. Can I access this dataset from Reporting Services somehow ? I think this is possible although I haven't seen any examples/explanations of how to do it??

    thanks

    In theory you can write SQL for the two tables in SQL server by linking dbs

Share This Page