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"

 

 

I know sounds like a silly question, but I have a reason.  In our app, the app gets the data from the database and then passes it to Crystal.  Crystal does know where the data came from, and luckily doesn't care, it just displays. So if you were using an app, you could do the same.

 

The other thought that I have is to use stored procs and have a formula for every field that would select the correct stored proc's field and use the formulas everywhere instead of the fields themselves.  This way, when you group, it would be grouping on the 'correct' field.  In a way...no it would be a complete pain to code, but it would make maintenance easier as there would only be 1 report to fix instead of 3.

 

Hope this helps.  Maybe someone has a more eleqant solution....

 

Oh, here is an idea tha might make life really nice...Make a stored procedure in one database.  Based on the parameter passed in, and the store would be a parameter, have the stored proc fetch the data from the correct database.  Now, the datasource and the columns are constant for Crystal, and life should be easy, the report is just a 'normal' report.

 

I haven't done this, but I pretty sure that the calling convention in SQL is database.owner.table, so shop1.dbo.table, shop2.dbo.table would give  instead of just dbo.table.

 

Hope this helps, it should make things much simpler.

 

I use CRXI rv2 to run the reports and get the data straight from MS SQL no other apps involved i'm afraid :(

 

The thing you sudjest to work with formula's, I did that already (only using formula's in the details and use a select case depending on the shop parameter field, to get the correct db field)

 

Examples:

 Naam: mailto:%7b@Aantal - {@Aantal }
 Formule: SELECT {?Shop}
 CASE "LEU" :{LEU-Hisarel.h_aantal}
 CASE "GEN" :{GEN-Hisarel.h_aantal}
 CASE "ANT" :{ANT-Hisarel.h_aantal}

 

 Naam: mailto:%7b@Marge - {@Marge %}
 Formule: SELECT {?Shop}
 CASE "LEU" :(({LEU-Hisarel.h_eenheid}-{LEU-Hisarel.h_inkoop})/({LEU-Hisarel.h_inkoop}+0.000001))*100
 CASE "GEN" :(({GEN-Hisarel.h_eenheid}-{GEN-Hisarel.h_inkoop})/({GEN-Hisarel.h_inkoop}+0.000001))*100
 CASE "ANT" :(({ANT-Hisarel.h_eenheid}-{ANT-Hisarel.h_inkoop})/({ANT-Hisarel.h_inkoop}+0.000001))*100

 

 

where shop is the parameter field with value LEU / GEN / ANT for our current 3 shops.

 

i use the formula name then to do the group by.

 

 

Already thanks a lot for the imput