Contact US

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.

Students Click Here

Linking DBs in CR 11

Linking DBs in CR 11

Linking DBs in CR 11


I just love this site...! bigsmile
What I don't love is our IT department who decided that the databases I use to extract some statistics no longer need to have the same data form in the only two fields (one from each DB) I use to define my reports.
I work as a system admin on a hospital and the two DBs are one for image data and one for data concerning the booking for these image data.
The workflow is started with someone creating a request that results in a booking in the first DB (RIS database). When the patients get their examinations done images are sent to the second DB (PACS database).
Each exam is identified by a unique(!) accession number. Except that recently this number gets a prefix ('DKRIG') in the second DB.
When we work with CR I know that it's possible to define that we only ned e.g. the last 8 characters from a certain field but do we have the same option when linking the databases?

Best regards, Mads Stiig

RE: Linking DBs in CR 11

Two possibilities come to mind, although someone else might have a better idea:

1- Use a SQL expression in the database with the longer number to remove the first five characters. Depending upon your DB, it might be left() or its equivalent. Then use this expression to link one database to the number in the second database (that you have added in a subreport) as a subreport link.

2A- If you are able to use the same driver for both DBs, try creating the report using a command where you again use a form of the left function to link one table to the other within the command.

2B- Or, try using a union all statement where you use the shortened field as one of multiple fields selected from the DB to merge the fields from the DBs into one table. For union statements, the corresponding fields must be in the same order, with the same datatype and format. You can use the word “null” (without quotes) to maintain the correct order for fields that don’t have a corresponding field in the other DB. If you need to know which DB a particular value is coming from, add a “type” field to act a label.


RE: Linking DBs in CR 11

That was a great idea, LBASS but alas I only have read-only access to the databases.
Though you gave me an idea. Since I only need to get data from at a max one month's exams I could do a report with data from the images from that month containing the necessary fields, save this as an excel sheet and then use this sheet as my second database - with a little 'cleaning up' I can get rid of the prefix.
Thanks for the answer bigsmile

RE: Linking DBs in CR 11

The suggestions that Lbass made do not require any other access than read only. A knowledge of SQL statements would benefit your issue. Unfortunately not all databases uses ANSI standards, so you may need to do some research on the LEFT function (or it's equivalent) for your database.

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! Already a Member? Login

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