How to show records from two different databases in Crystal Reports 2008?

[Former Member]

Hi,

I am a new bie to crystal reports and I am trying to create a report from same table which is from different databases.

I am working on Crystal Reports 2008 and I connected two connections thro Data Base expert. I have two connections now with same table fields say a.empname, a.empsalary from table1 and b.empname, b.empsalary from table2.

I want to render this records in a same report as rows. (both a.empname a.salary as 1st row, b.empname, b.salary as second row in the report)

I tried two details part but it is possible to display the records as rows from two tables in different databases. but the problem if there is a third database having same table and i want to display c.empname , c.salary again in have to go for design.

How to figure out this problem ?

Thanks

Murali Sri

[raghavendra_hullur]

Hi Sridharan,

Is it like you need to display the records as separate data? I mean on 2 different rows as separate fields?

If not, you can write a query to select the records from both tables with proper join conditions and then display them using single field for each database column instead of going for 2 different fields for each field (i.e. empname, empsalary etc.).

Even the same applies to your question on adding the 3rd table and displaying the field again.

All in all, if the fields to be displayed are same but coming from different tables, you can join the tables and create a query and get the records displayed in the report.

I hope this helps.

Thanks,

- Raghavendra

[Former Member]

Hi Ragavendra,

Its not possible to do join on tables because they are same table but different databases without any references. i dont know if we can join tables inside Crystal report.

I will explain you the scenario more clearly.

Database - A having Employee table emp_tbl

Database - B having Employee table emp_tbl

I am having a crystal report here and i want to display like this

Emp name Emp salary (Same header section for both)

Shankar 6000 (from database a - emp_tbl table)

Ramesh 7000 (from database b - emp_tbl table)

Thanks

Murali Sri

[raghavendra_hullur]

Hi Murali,

Got it.

So you need to display data from 2 different databases.

Well, using same header can be achieved, as you can use header labels in page header section and can display the data from 2 different databases using a sub report for each. In that way, you can make sure that all the records from a particular database will be displayed in required order.

And if you want to go for 3rd database details to be displayed, you need to add another sub report and just modify the query accordingly to reflect changes if any required.

Thanks,

- Raghavendra

[Former Member]

Raghu,

Thanks for the reply,

I have a concern, But if you are connecting a another database you have to create one more subreport and each time am creating connections across databases we have to create subreport and go for the design to show the record. Is it any way to have a common field or union records approach in crystal report 2008.

For Example :

Approach 1 :

-

---

Emp name Emp salary (Common Header)

-

---

Variable Field1 Variable field2 ( Iterations to be done here for records)

Variable field 1 contains ---> emptbl(employee name), b.emptbl(employee name) (A database, b database, c database or any .db)

Variable field2 contains ---> emptbl (salary info between databases)

Approach 2:

-

---

getting all the records and union all the records

Thanks

Murali Sri

[raghavendra_hullur]

Murali,

I believe that is not possible in Crystal.

One approach can be to connect to different databases from front end (e.g Java or .net) and build business logic over there to get the records and then pass the merged data to Crystal.

In answer to your question of designing each sub report, if all your different databases are having same column names for the fields you are going to use in the report, you can create one report and use it as sub report for different databases, so that you don't have to even design again and again.

But, if the column names are different, then obviously you have to modify the query in order to change column names.

Thanks,

Raghavendra