dynamically change database source?

[alvintse]

I have created a report with CR9 using store procedure that pointing to the database.
My problem is when the report passed to different users, the application will point to a different database name. I cannot use the set datasource location because the report is running on the user machine and we cannot request the user to do any extra work.
I just wonder if there any way I can solve this problem, may be have another parameter asking the user the name of the database to be connected?

Any suggestion?

 

[fredp1]

I have seen it done in V9 but i'm a bit vauge on the details.
I think the repository was used to store a variable, with the variable changing depending on a formulae.
It can be done, hopefully someone will tell you how to do it exactly.
Fred

 

[FVTrainer]

What is the backend database? Also, when you say that you cannot use the set datasource location because the report is running on the user machine, what exactly do you mean. Is the report being launched by something like a vb app? From Crystal Reports loaded locally? How is it being run?

 

[alvintse]

The backend database is mssql 2000. Yes, the report run within a VB application.
My responsibility is to create the report for that application. Usually,I create the report with the same server name and database name as the application connected to. Or they can set the datasoure location after I passed the reports to them.
I don't know exactly how the application will handle the reports. They just want me to create the report with the ability for the user to change the name of the database without any extra work by the user.
May be at least change the name of the store procedure (same store procedure but with different name).

I know that is kind of confusing.

 

[fredp1]

I think how it can be done was to use the repository to store a sql command. You can then include variables in the sql command to change the "from" statement.

You'll have to be veryy carefull when you contruct the sql command to use the Crystal syntax.

have a look at this link for a example

http://support.crystaldecisions.com/library/kbase/articles/c2011826.asp

Cheers

 

[fredp1]

here is a specifc example of what you are trying to do.

http://support.crystaldecisions.com/library/kbase/articles/c2012030.asp

Cheers
Fred

 

[FVTrainer]

fredp1 provides an interesting answer, but how does your app deal with loging onto the database, or, for that matter, passing a parameter value? You'll still need to tell the report itself (i.e., when logging onto the server) where the server is, and if your app can deal with that, why can't it use the set location method? The main reason I bring this up is that command objects, while useful and flexible, are going to run slower than a stored proc, so if performance is an issue, the stored proc is preferred. Does using the command object with a variable meet your need?

 

[alvintse]

FTTrainer,
I agreed with you. Using Command object is much slower than using store procedure. Beside, it is not the solution for my problem. I think you really understand my problem. Do you (anyone) have any idea for how to deal with it?

Thanks.

 

[FVTrainer]

Well, can you answer the question I asked in my last post? Specifically, how does your app handle logging onto the database and how is that info passed into the report? At runtime on the user's workstation, does the application have absolutely no knowledge of the SQL Server that the report will run against? I realize you are not the developer of the application, but somehow the application has to tell the report what server and database to log into. Or, the application has to collect a recordset (using ADO or the Crystal Data Object) and pass it into the report (in this case the report has no database connectivity; instead you'd be writing a stored procedure to be used by the app, not by Crystal). Anyway, I really can't make a good suggestion until I know how your app is handling the issue of providing logon info to Crystal.

One last question: Where will the stored proc you are writing be installed? On the database you are using to design the report, or on the database the user will use, or both.