Crystal Reports with tables from two databases

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
gibneytAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gibneytAuthor Commented:
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
vastoCommented:
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
gibneytAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

vastoCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gibneytAuthor Commented:
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
gibneytAuthor Commented:
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
gibneytAuthor Commented:
Answer is the new View shows up just like a table.

Thanks Vasto.  Works to spec!
0
gibneytAuthor Commented:
These little nuggets are the hardest ones to find.  Thanks Vasto for the new neurons!  Once again, E-E to the rescue.

Tim
0
vastoCommented:
You are welcome :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.