Union of tables from different data sources

[russula]

Hello All,

I have two commands on two different data sources (can't create one query with union inside).

I have to merge all data into one data set. Actually data from one data source cover gap in the data from other data source. I need to create one graph with all data.

Is it possible somehow in Crystal Report XI? If could use SQL, i would do UNION ALL but it is not the case because of different data sources.

Thanks in advance,
Anna

 

[IdoMillet]

One approach is to link (not import) to both data sources from MS Access and do the union in Access. Then, use Access as the data source for the report.

If one of the original data sources can link, you can simply link in that DBMS instead.

hth,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[russula]

Unfortunatelly i can neither use Access nor db link in this case, that's why i was trying to find solution in CR. At the moment it looks like i have to set up special table in Oracle db for it and load data into this table.
As far as i understand there is no solution in CR?

 

[IdoMillet]

The only other alternative I'm aware of is to use one of the 3rd-party Crystal Reports Desktop Scheduling tools listed at:

http://www.kenhamady.com/bookmarks.html

With that particular tool you can schedule trigger a batch file that would take care of:

1. export Crystal Report #1 using your data source A to a "my_union" table in any target database via ODBC. If the table already exists, you can set the process to REPLACE it with the new data.

2. export a Crystal Report #2 using your data source B to the same "my_union" table in the target database via ODBC. You would set that process to APPEND the new data to the existing records in that table.

You can then schedule or interactively run Report #3 against the "my_union" table.

Note: while any Crystal report tool should be able to create a table export via ODBC (you simply select ODBC as the export format), most tools can't handle a case where the table already exists. The unique feature of that tool is that can Replace or Append to existing tables.

hth,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com