INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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,

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

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close