Multiple Databases solutions for crystal?

[Nezpro]

Hey,

Currently i have 5 identical sql databases, The data is separated for reasons beyond my control. I have created many reports in crystal XI by first creating a report that meets the criteria, this is done for only one database. I then copy and past the sql statement into a new Command and UNION ALL the databases with the exact same SQL (just change the DB). This allows me to get complete corporate sales figures.

I problem arose with they added another database, now i had to go back and edit about 75 reports to make sure they all included the new db. I want to centralize this, so i only need to change the data in one place and all reports get the update.

Is there a way that i could send a SQL statement to a "Middleware" and it takes the sql statement and sends it to all my db's and then unionizes the result. (could be slow)

I use and 4gl informix db (old but the only that works with the invoicing software) and unions in views is not an option.

So i've seen people suggesting creating an access link table and running the query off of this. Is this a good and solid solutions? I've also been approved to get Crystal Server XI and it comes with Business View Manager i've had a quick look at it in the eval copy but still not sure how it all works.

any help would be greatly appreciated

thx Dan

 

[Turkbear]

Hi,
Create a view or SP that does the UNION so that your reports can all use that view instead of the Command Object..
When you need to alter the view/SP you can do so, and all the reports ( as long as the name, # and type of data fields stays the same) will use the new version..

The are 3rd party tools that will allow you to update all the reports to use the new datasource..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dgillz]

Turkbear,

Please educate me. I thought a UNION would union different tables in a single database, but it could not union tables from 5 different databases like Dan asked for.

Am I missing something?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[Turkbear]

Hi,
Many databases ( not sure about SqlServer) have a method to create a link to other databases of the same type ( using ODBC maybe or ,as is the case with Oracle, a native db link connection).

If no native SqlServer technique exists, you can create a link to each of them in Access and use Access to create the UNION query and report on that...Update the Access one as needed.










To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Nezpro]

the 5 databases are on the same server so all i do is

select * from db1:tablename where ...
union all (
select * from db2:tablename where ...
)
etc..

they are identical databases, just different instances of it, but yes they are different DB.

 

[Nezpro]

Turkbear,

Creating a view was my thought as well however this DB won't allow unions in a view,

===========================================
Because it must be merged with the user's query, the SELECT statement on which a view is based cannot contain any of the following clauses:

UNION
The user's query might contain UNION. No meaning has been defined for nested UNION clauses.
============================================

so i'm not sure how else i would do it with views. As for a SP would i create a temp table to report against?

thanks again

Dan

 

[Turkbear]

Hi,
Probably yes..I am not familiar with Informix's functions but that seems to be what you would need..However, I am not sure how Informix would handle it when one or more of the joined tables changes..

If you can bring links to the tables into Access ( if you have it) using an ODBC DSN, it might give you more flexibility.



To Paraphrase:"The Help you get is proportional to the Help you give.."