3 databases, 3 reports, 1 .rpt file?

[mgason]

Hi,
can a CRXI report be constructed so that it can dynamically be made to look at different databases?

I have many reports that work perfectly with database1 which holds imformation for factory1. Now identical databases have been added as there are new factories.
databases 2 and 3.

Do I need to create 3 versions of each report? This will make maintenance a drag, especially as there may well be more versions in the future.

can the database to look in be a parameter or some such trick?

any ideas on how to handle this
thanks
mark

 

[synapsevampire]

Try posting the type of database...

You might use a Command Object (listed as Add Command under any SQL database type), and replace the database name with a parameter.

Or you might use a Stored Procedure and pass the database, or perhaps even a View might be used.

-k

 

[mgason]

Sorry,
thanks for the reply
it is a microsoft SQL server 2005 database.
what method might be the best choice?
mark

 

[synapsevampire]

Try the command Object if they're on the same server.

-k

 

[mgason]

Thanks,
I admit to being a bit of a novice, especialy on the SQL end. There are some people here who know more about that, but I would not say we have a real SQL expert.

Can you point me to any info on this, maybe a tutorial or example that I can look at and pass on to the database people. I would like to have a little idea of what I am talking about before going to them.
thanks
mark

 

[lbass]

SV was suggesting you could use a command as your datasource where you use a parameter in place of the table name. One way you could try this would be to take an existing report that you want to spin out to the other databases, and go into database->show SQL query and copy the query. Then paste the query into the "add command" area of a new report (blank report->your datasource->add command). Then within the command, create a string parameter {?whichtable} and replace the table names wherever they appear in the query, so that the query would then look something like:

Select {?whichtable}.`field1`,{?whichtable}.`field2`
From {?whichtable}
Where {?whichtable}.`field1` = 'x'

The punctuation would be specific to your datasource, but you wouldn't even have to deal with that--just substitute the parameter for the table names. This is something you could try on your own.

-LB

 

[mgason]

Thanks for the help,
Not sure if I failed to explain myself properly or I am not understanding or both!

I did as lbass suggested.
when I did it seemed I was a bit on the wrong track.
as explained in your post I am changing the table name using {?whichtable}, is that correct?

My table and field names are identical across the 3 databases.

I need to connect to database 1, 2 or 3 depending on which factory the user selects.
The ODBC connection name and database name are different.
Login and password are the same for all 3.

thanks again
mark

 

[lbass]

If you look at the SQL queries for reports for different factories, is the difference then only in the owner? This would appear in the "From" clause of the query, like:

From "Owner"."table"."field"

Not sure why you would be using different drivers (ODBC connections).

-LB

 

[mgason]

Yes the only difference is in the From part of the query.

I may have been unclear when I said different ODBC connections.

If I was to start a new report in crystal for say factory2 I would go to File>New>standard report
In the dialog I would pick New Connection and point to factory2 on the list which would also show 1 and 3.

This would then appear as a little cylinder icon with a plug

that woud show MachineData2 as a database.

Is factory2 then a an ODBC connection?
If so is this the wrong way to go about it, 3 ODBC connections?

thanks
mark

 

[lbass]

I'm not sure, but it looks like you might have separately named drivers for each factory. If that's the case, maybe you could just use database->set location to change drivers (and therefore) databases for a particular report. Have you tried that to see if that works?

-LB

 

[mgason]

I think we are starting to get things clear.
Yes I can go to database>set location and easily create a new report that looks at another factory.

The problem is I now have 3 reports, 1 for each factory. There are dozens of reports.
If I have to make layout changes to a report I have to change 3 reports.

These reports are deployed to new customers each time we do a project, so it is quite normal that I would have to change layout etc in all of the reports. Having 3 times as many (and I know we have a customer coming up with 5 factories) is a serious problem.

Is there a way around this?
Are we doing something fundamentally wrong that is making things harder for us?
thanks
mark

 

[lbass]

No, you do not have to create a new report. Set location only changes the database connection, so you can set it to factory1, run it, set it to factory2, run it, etc. Same report.

I guess the issue is deployment. If it were me, I would maintain one report, and then either set location and deploy the updated version per customer, or train the customers to set location.

Perhaps someone else will weigh in.

-LB

 

[mgason]

Ok,
the database for factory1 conection is MachineData1
the database for factory2 conection is MachineData2

the report runs inside a VB application.
I set the database and Login etc from there.
This works if it is the one I set when building the report, ie. factory1. In Vb it is...

report1.Database.LogOnServer "p2sodbc.dll", "factory1", "MachineData1", "user", "password"

I can not set it to be factory2 though, I get a connection failure when I run the report which is looking to connect to factory1.

The 1 customer may have 3 factories and need to get reports from each one, manually changing the database connection in crystal will not work. The customer need not even have Crystal.

can "From" be supplied as a parameter, will that even help?
mark

 

[lbass]

That was some important info to leave out--that you were running this inside VB and that the customers don't even necessarily have CR. I'm not sure how set location works in that case. Someone else will have to help you. Sorry.

-LB

 

[mgason]

true,
but I was looking for a purely Crystal based solution such as letting the user enter a parameter that defined the database.
I was deliberately wanting to avoid ideas that involved visual basic.
That is a path I am following seperately, but I wanted to know if it could be done solely in Crystal.
The answer it seems may be no.
thanks for all your efforts
mark

 

[lbass]

You still might be able to use the parameter option. Please show a sample of two SQL queries, one for factory1, and one for factory2, as shown in the database->show SQL query area.

-LB