Solved

Crystal Reports with tables from two databases

Posted on 2014-10-15
9
202 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

757 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

20 Experts available now in Live!

Get 1:1 Help Now