How to display data from two databases in RDL report?

Question

I'm creating a report that will take some data from one database (Company and list of Customer for every Company) and some data connected to it from other database (list of Item for every Customer). It seems that displayed data can only use one DataSource and that a DataSource is a connection to one database. I did some googling and read about subreports and I tried this solution but the report doesn't look the same (for example I have columns Company, Customer and Item, where the subreport is, and subreport contains two columns and I wouldn't like them to be displayed in one column...). Are subreports really only way to display cross-databases data?

Answer 1

Hi, i have done somethin like this using subreports in rdlc. I am not shure if it is possible to be done in rdl but in rdlc every subreport is actualy report with it's own DataSource. Best Regards, Iordan

Answer 2

Yes a single report control (table, list etc) can only display data from one dataset. You do have a couple of options though.

You can reference multiple databases from a single dataset if they are on the same server or if one server is linked to the other. For example if you have both databases (company, customerdata) on the same server then

Select co.CompanyName, cu.CustomerName, ci.CustomerItem1
From company.dbo.Companies co
      Inner Join company.dbo.Customers cu On cu.CompanyID = co.CompanyID
      Inner Join customerdata.dbo.CustomerItems ci On ci.CustomerID = cu.CostomerID

If the databases are of different servers but are linked then you can refer to the tables using servername.databasename.schema.tablename instead. In both cases you need to make sure the login you're using has appropriate permissions on both sides.

Alternatively you could use SSIS to pull the tables from both databases into a common database prior to running the report perhaps scheduling to run overnight or periodically during the day.