Solved

Crystal Reports with tables from two databases

Posted on 2014-10-15
9
207 Views
Last Modified: 2014-10-15
I have my main MRP db and I have to augment that data with another small db with only a few tables.  I have added the tables to the report.  When I join that table from the small db I can only to a = join.  When I try to add fields from the small db based on that join nothing appears yet I can browse the field in Browse Field Data.  What am i missing?

TIA,
Tim
0
Comment
Question by:gibneyt
  • 6
  • 3
9 Comments
 

Author Comment

by:gibneyt
ID: 40383253
So I just read the article Crystal Reports: 3 New Uses For Sub Reports.  Apparently I can only have on set of tables in a report so it looks like I'll need to run a subreport to suck the data in the data from the small db initially.  That subreport will apparently put the data into an array.  How is that done and then how do I equate (join?) the like field?

Tim
0
 
LVL 18

Expert Comment

by:vasto
ID: 40383297
You can use multiple databases in one report. It is not necessary to use a subreport. However, the performance will e bad because Crystal will try to link the tables and all operations will be processed on the local computer. You can link the tables from your second database in the first database and process the data using the database engine. There are multiple ways to do that. For example if db is MS Access you can link tables from other sources. If your db is SQLServer you can create linked servers etc. This will allow you to create a command and to access the data using one connection.  What type is your database ?
0
 

Author Comment

by:gibneyt
ID: 40383300
They are both SQL dbs.  The MRP db is a professional db and the small db is of my own making.  I was able to link them in Visual Linking Expert but with the link type (=) I could never see the data from the small db.  I am using CR 8.5 and stuck with it by the way.
0
 
LVL 18

Accepted Solution

by:
vasto earned 500 total points
ID: 40383324
Don't do this is on Crystal. Are both databases on the same server ? If they are you can create a view like this (just a sample)

Select .... FROM DB1.dbo.table1 t LEFT JOIN DB2.dbo.Table1 t2 ON  t1.ID=t2.ID
You can even remove "DB1.dbo." If you are going to callt the SQL in DB1

 If the databases are on different servers then you can create a linked server. Just choose one of the servers and link the other one., Here is an article how to do this: http://msdn.microsoft.com/en-us/library/ff772782.aspx

Once the linked server is created you can use a sql like this:
Select .... FROM table1 t LEFT JOIN LinkedServerName.DB2.dbo.Table1 t2 ON  t1.ID=t2.ID
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:gibneyt
ID: 40383363
Does this look like the correct syntax?

SELECT     ACTTYP_104, DESCRPTN_104, UDFKEY_104, UDFREF_104
FROM         RMTEST.dbo.Account_Types t1 LEFT JOIN HWPL.dbo.PRODUCTION_GROUPS t2 ON  t1.UDFKEY_104=t2.COST_CENTER

When I run the view after making the query look like this:

SELECT     t1.ACTTYP_104, t1.DESCRPTN_104, t1.UDFKEY_104, t1.UDFREF_104, t2.NAME, t2.CONTACT, t2.COST_CENTER, t2.PLANNER_ID
FROM         dbo.Account_Types AS t1 LEFT OUTER JOIN
                      HWPL.dbo.PRODUCTION_GROUPS AS t2 ON t1.UDFKEY_104 = t2.COST_CENTER

The NAME, CONTACT, COST_CENTER, and PLANNER_ID come back as null.

Alas, I didn't look down far enough.  Where UDFKEY_104 is blank of course the other fields will be null.
0
 

Author Comment

by:gibneyt
ID: 40383377
Both dbs are on the same server.  Now that I have a new view in the MRP db how do I use that view in CR?
0
 

Author Comment

by:gibneyt
ID: 40383394
Answer is the new View shows up just like a table.

Thanks Vasto.  Works to spec!
0
 

Author Closing Comment

by:gibneyt
ID: 40383395
These little nuggets are the hardest ones to find.  Thanks Vasto for the new neurons!  Once again, E-E to the rescue.

Tim
0
 
LVL 18

Expert Comment

by:vasto
ID: 40383406
You are welcome :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now