Using one report for multiple databases

[Pilekes]

Hi, I've got the following problem:
I've got 8 databases which are all similar, but they've got information from different firms... If I were to put all this data in one database I'd have to reprogram my entire program, which at this moment is not an option.
I'm trying to use 1 report for each database, so when I'm logged on to Firm A I want to see only their data and when logged on to Firm B only theirs.

For the record I'm using Crystal Reports 9 with Visual Basic 6 and an MSSQL-server.

Obviously I've made my report based on the Firm A database.
And the report works fine when I'm logged on to that database, but when I switch databases either I get nothing on my screen or I get the data from the Firm A database...
This is officially driving me insane...

Here is the code I'm using.

Public Appl As New CRAXDRT.Application
Public Report As New CRAXDRT.Report
Public ConnectionInfo As CRAXDRT.ConnectionProperties
Public CRXDATABASETABLE As CRAXDRT.DatabaseTable
Dim ReportRs As New ADODB.Recordset


Set Report = Appl.OpenReport("C:\Report.rpt")
Set ConnectionInfo = Report.Database.Tables(1).ConnectionProperties
ConnectionInfo.Item("Data Source") = "Servername"
ConnectionInfo.Item("Initial catalog") = "Database"
ConnectionInfo.Item("User ID") = "***"
ConnectionInfo.Item("Password") = "***"

I've also tried the following:
For Each CRXDATABASETABLE In Report.Database.Tables
CRXDATABASETABLE.ConnectionProperties.DeleteAll
CRXDATABASETABLE.ConnectionProperties.Add "Provider", "sqloledb"
CRXDATABASETABLE.ConnectionProperties.Add "Database Type", "OLEDB (ADO)"
CRXDATABASETABLE.ConnectionProperties.Add "Data source", "Servername"
CRXDATABASETABLE.ConnectionProperties.Add "Initial Catalog", "Database"
CRXDATABASETABLE.ConnectionProperties.Add "user id", "***"
CRXDATABASETABLE.ConnectionProperties.Add "password", "***"
Next CRXDATABASETABLE

Report.DiscardSavedData
Set ReportRs = Conn.Execute("Select " & SQLSelect & " from " & SQLTables & SQLWhere & SqlText)
(The SQL variables are filled correctly)

Report.Database.SetDataSource ReportRs
Report.PaperOrientation = crPortrait
CRViewer91.ReportSource = Report

CRViewer91.ViewReport

-----------

Anyone got a clue why the database in the report just won't change?

 

[synapsevampire]

You can switch your connectivity within the report itself to use a Command Object, and make the database name a parameter, then just have your code set the parameter.

This will work from within Crystal or via code.

-k

 

[Pilekes]

Uhm I can't quite figure out where you are able to make the database a parameter...
When I read your post this morning I immediatly started to search the internet
And I found this, at least I think this is what you mean:

http://publib.boulder.ibm.com/infoc...iningvirtualtablesbasedonacommandsqlquery.htm

But now I added the Command Object to the repository, but it seems to me like I still can't make the database variable...

Please explain

 

[Pilekes]

Allright...
So I did use the Command Object and copy-pasted my already existing Crystal SQL-query in there and added the command to my Crystal Repository.

Then I replaced all my existing fields on the report with the fields from the Command Object. (and in all the formulas as well)
After that I removed the old database objects on the Database Expert screen, so I only have the newly made repository Command Object left... And it works!
Thanks!

But this reconfirms my previous observation:
You can't switch databases if you use more than 1 table in Crystal Reports...

Now I can only hope that my users can use the reports as well and they won't miss the repository-link or something like that :X
(and that Citrix accepts this as well....)

 

[JuliaFarber]

Guys,

Could you tell in steps how to switch connectivity using Command Object.

Thank you.
Julia

 

[synapsevampire]

You don't change connectivity, you can change the database.

Insert a parameter for the database name, and then change it in code.

I suggest that you start a new thread and include technical information:

Crystal version
Database/connectivity
Code (what language, if you're using code)

-k