Can report use different logon (schemas) in CE???

[KellyStee]

We have about 40 clients that are set up on forty different schemas (all are identical) in Oracle 8i. We want to create a report that can be accessed from the Crystal Management Console and depending on the logon/password (i.e., user name/password) that you enter in CE, you can hit a different schema and view a particular client's data.
I was told in order to do this, after the report is created (in Designer), I should delete the first part of text in the 'Table' field of the 'Set Location' (also in Designer). This value is usally user_name.table_name, so I would delete the user_name portion, so that the report can connect to any user name/schema which would be specified in the Management Console under 'Databases'. I did this with two reports, and it worked! I can connect them to any schemas and see that particular set of data. The problem is, deleting the user_name from the 'Table' box in the 'Set Location' menu, sometimes causes me many many errors. Can you actually do this?!?! Crystal tells me it's not supported. It seems if I leave the user_name in the 'Set Location' box, then it's "hard-coded" and I would never be able to logon to another schema through CE, since I would be choosing a different user name. Please tell me I don't actually have to create 40 versions of this report, one for each client?!

Thanks in advance!!
Kelly
kelly.steensma@us.pwcglobal.com

 

[Infobiggot]

I would suggest you take a look at either a stored procedure that changes the schema or invesitgate using synonyms to achive this goal.

It has been a long time since I have tried this in oracle

 

[KellyStee]

Since I last posted this issue, I have found an easier solution to my problem. The Crystal Enterprise "unsupported" solution is just to delete the hard-coded schema name in the Set Location dialog box. Since we had three different Set Location's (due to two subreports and the main report) for each report, we would consistently get errors and corrupted files from doing this 3 different times. We now use an ODBC connection for the two subreports and strip the schema name and a native driver connection for the main report and again strip the schema name. We have not encountered any errors using this method and have had a 100% success rate.
We were told, though, that if the schema that is being accessed lies on a different server, there is an add-on package available that would allow the report to switch schemas and servers, but the method we currently use will not allow us to do this.

Let me know if this helps.
Thanks!
Kelly Steensma
PricewaterhouseCoopers
kelly.steensma@us.pwcglobal.com

 

[KellyStee]

By the way, what this allows me to do, is to connect to different schemas from Crystal Management Console and my GUI.

Kelly