Multiple ODBC on demand - choice at runtime

[rnd]

CR 9/10/11 SQL Server 2K/2K5 (I use them all)
I have 4 subreports in a report. One of the sub reports has a different datasource (ODBC/server/database) depending on the Company. Therefore, I have to change this one subreport for each company. Rest of the main & other sub reports are all the same for every body. Thus I end up maintaining 3 versions (since we have 3 companies this sub report is changed for). I have to login twice -one for each ODBC for each version separately. The report works fine.
Is there a way I can consolidate this into one version? I mean putting all 3 different versions of this particular sub-report in one report and invoking the relevant sub report at login (depending on the company). It should prompt me for which company I belong to via a parameter or some thing. For example, if I respond Comapny-A it will open the ODBC login for Company-A. If I respond Company-B it will open the ODBC for Comapny-B etc.
The object is only one report version and only one of this particular sub report at run time. Every body runs the same report but gets the relevant login and data with out having to have different/separte versions for each.
Is it possible? (On-demand subreport is not an option since it won't maintain the integrity of the sequence.

 

[satinsilhouette]

The only way I know of solving this in Crystal is the way you describe. Otherwise I would do this in a Business View.

Thanks so much!
satinsilhouette

 

[rnd]

Sorry I forgot to mention that Business View or Command Object is not an option either.

 

[synapsevampire]

You can insert the subreport 3 times into seperate sections and conditionally suppress the sections based on which company they choose.

However I think it will prompt at runtime for each connection.

Odd that you just don't set up a single datasource as in a View or SP which returns the data from any of the three databases...

Bad idea to handle this on the client side.

-k

 

[rnd]

Naturally. It is prompting for all ODBC connections. The user won't have the other two login ID & PWD.

I agree this is not a smart idea to handle this on the client side. We can't do it on the server side because each other sub-report is from different server/DB as well but common to all. But when you centralize things that are scattered around this is what we are up against. If it is run on an application there won't be any problem. Unfortunately, every user is a "Crystal wiz" and they run it in CR designer.

 

[synapsevampire]

You might set up a user/pw in one of the databases for all 3 databases (no permissions to the objects), and then use an SP as the data source to extract the data from the appropriate database based on the connectivity provided if you really need this to operate in one report, otherwise you need different reports.

-k