Data from Multiple sources

[slakshma]

Hi,

I'm a novice in CR. Just started to use it. We are using AS/400 in a distributed environment. ie. Each branch has their own AS/400. But the users want consolidated reports. Is there any way I can report on all the data on the same report without extracting them seperately to a consolidated database?

 

[paulmarr]

Hi slakshma,

Yes you can! - There are a couple of ways to accomplish this depending on your situation.

You can join tables from different datasourcs together via a common field(s) or you can report on each datasource separately - one in the main report and othe others with subreports. (which are reports inside the main report)

I hope this helps

paulmarr

 

[slakshma]

Hi Paulmarr,

Thanks for your tips. Could you expand a little bit more on how to pull data through a common field(s)??..looks like that approach might work better for me than the sub reports

Thanks in advance

 

[paulmarr]

Sure,

Connect to your first data source and select the tables you want. Then - while still in the Database Expert dialog box - choose your next data source and add the tables you need from there.

Go to the linking screen and link table from datasource 1 to table from datasource 2 via a common field - this really depends on your databases and data, etc. For instance Customer_Id to Customer_Id.

Then design your report!

I hope this helps!

paulmarr

 

[slakshma]

Hi Paulmarr,
Sorry to bother you again....

Say, if I have 2 customers tables. Should I have another cutomer linking table (where I should have the common linking field?)to which I have to link these custimer ids.

Still I'm little bit confused....

Sorry again

Thanks in advance

 

[GJParker]

If you have a customer table on each datasource both with the same structure but holding different data, you wouldn't really want to join these together. You would instead use a UNION query to select the data from each table and add them together i.e.

Select * From DatasourceA.Customers
UNION
Select * From DatasourceB.Customers

Thisi could get rather messy depending on the number of datasources you have.

HTH

Gary Parker
MIS Data Analyst
Manchester, England