multiple Sources

[AdamField]

Hey All,

I'm stuck with quite a big problem and i hope this way will will get me a answer.
I'll try to explain the problem as good as possible but feel free to ask more info if needed:

First, the company i work for has 3 shops (lets call them shop1 - 2 - 3) and the program we work witch for our accauntency / stock / orders works with flat file DBF files.
Every night we transform the information from like 15 dbf files into a SQL server.
this is split up in 3 databases in MS SQL
Shop1: with all the tables (artikles / invoices / orders / .....)
Shop2: with the same tables just diferent info
Shop3: again the same tables, diferent info

@ the moment when i have to make a report i have to make it 3 times and switch the names of all the sourse fields to the correct shop number. This is not a fun thing to do aspecialy when using reports with lots of group fields that get f**ked up the moment a sourse field is gone

So i tried to load in the tables from all 3 the shops @ the same time and link them where needed under the same shop but there is no link i can use over the 3 shops. and then use a parameter field where i can let the user pick a shop and he then uses the correct tables from that shop.

This keeps getting me errors (somthing like : the current bridgeconfiguration has more then one start point, this is not supported most of the time)on the group field and the like. So i'm hoping somebody can point me in the right direction on how to make reports that are database independant and just pick the database i need depending on the shop given in the parameter field.

Hope this makes a bit of sence

Greetings Tom


Ps: sorry about the writing mistakes i normaly don't speak/write english.

PS2: i have a report definition TXT file from my report if that could help
The error i get when running the report now is "fetching the info from the database failed

 

[IanWaterman]

When you load into MSSQL why not just create a single DB and when you insert fields into each table just add another cloumn which will ID each shop.

YOu can the run reports from a single datasource.

If you need 3 DBs for another reason then make a 4th just for reporting purposes.

Ian

 

[AdamField]

Hey Ian,

I have to split them in diferent db's becouse all the numbers from invoices / orders / delivery's are the same number layout (090000001 / 090000002 / ...). The same for almost all other info (it would be a nightmare to rename all the column's in the SQL and rework XXX number of reports)

There is no way to just use multiple datasources ?

Greets

Adam

 

[IanWaterman]

Not asking you to rename all columns, just add a single column to each table as required so that you can uniqely ID each shop.

YOu probably will not need to add to every table, just those holding order, transactions and invoice records.

Switching data sources at run time with Crystal is not easy, you need some sort of scheduling software and you can then make 3 copies of each report each pointing at the separate DBs.

Ian

 

[AdamField]

Hey Ian,

This exactly what i have now (3 copies of every report with diferent DB info). Bad thing is the guy before me made all those copies table dependant (aka db fields straight in the reports in stead of into a formula and grouping / working on the formula). This makes my job a bit more dificult when i adjust the report for a new shop..

I'll look into this and see if i can fix it with the 3 shops in 1 DB

Tnx for the info !!

Adam