Solved

Crystal Reports with tables from two databases

Posted on 2014-10-15
9
208 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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 …
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

805 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