Solved

Crystal Reports with tables from two databases

Posted on 2014-10-15
9
216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

728 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