Crystal Reports two data sources, objective is to sort by key field

[AndersonCJA]

I Have a Crystal Report that provides a correct listing. I Created a second report displaying the same type of information, but from another Data Source.
The only problem is that the Key field is sorting in order in one report, then in order within the second report, and the person who uses the report, wants a perfectly ordered list.
I attempted to use a command with a union, but I receive an error, Invalid Object. The command can use one Data Source or the other, but not both.
Is there a method in which both data sources can be accessed within one command?
Example:
select keyfieldA
from datasource01.table01 a
union
select keyfield
from datasource02.table003 b

Thank you,

 

[IanWaterman]

You will have to create a link from one database to another and then do your union in a single command. Not possible in Crystal.

You could also try to create two commands each with their own data source and then join commands in Crystal.
However, not sure how you would join data and then successfully list results without causing duplications.

Ian

 

[AndersonCJA]

Thank you IanWaterman for the reply.

I learned a lot yesterday and have it all working perfectly today.
This can be done using Crystal reports. The query is using 3 different data sources.
I did this using 11.0.0.1282 and using an ODBC connection.
I signed on to the Datasource 02 - and added the command and then moved the fields to the report.
It is amazing! I could not have figured this out on my own, my co-workers helped with the query syntax that make it work.
The key factors, 01. specify the Server name with the Datasource name. 02. Use the Collate feature on string or text items.

SELECT
a.fld01 collate database_default as name01,
a.fld04 as name02,
b.fld05 collate database_default as name03
FROM
Datasource02.admin.TABLE_a a
INNER JOIN Server2.Datasource_01.table_b b ON b.fld01 collate database_default = a.fld01
INNER JOIN Server1.Datasource02.admin.Table_C c ON a.fld02 = C.fld02 AND c.fld03 = 1
WHERE
b.fld = 'value'
union
SELECT
d.fld1 collate database_default,
0,
D.fld03
from
SERVER3.datasource3.datasourc3.Table4 d
WHERE
d.fld = 'anyvalue'
ORDER BY 2