Including multiple companies in Crystal Report 1

[MarshaPav]

Is there a way for Crystal Reports to include information from multiple companies? I have an ODBC set up for each company, but how do I set it up so I can combine the information from different companies?

 

[klewis10367]

Create a union view in sql enterpise manager.

 

[jazgeek]

Interesting though, but how would you specify a union join for 2 different databases? Also where would you deposit the union view?

-----------
and they wonder why they call it Great Pains!

jaz

 

[klewis10367]

Create the view in Enterprise manager under the any one of the databases you are using. After the view has been created you need to give it the proper permissions. After that is done you should see the view through your ODBC connection that you have created for the database that the view is under. Also, in crystal make sure when you connect to your odbc you select the properties and make sure that you have the box checked to show views. I am doing this from memory so the text might be different. Here is an example of a view created with different database:

CREATE view COHR2BEN21
AS
SELECT * FROM BC.DBO.HR2BEN21
UNION
SELECT * FROM EXECS.DBO.HR2BEN21
UNION
SELECT * FROM SBHOA.DBO.HR2BEN21

 

[jymm]

when I did something similar to this I included another field so that I could tell which company db it came from within the view (and so I could filter a company out if need be in Crystal).

I did not take all the fields (select *) and just had 'BC' or 'EXECS' at the end of the list as DB

so using the above...
Select Field1, Field2... 'BC' as DB
Union

helps me get the GL stuff I want...

 

[jazgeek]

crystal 9 and above allows to connect to multiple data sources.....




-----------
and they wonder why they call it Great Pains!

jaz