Report error involving dynamic parameters and table prefix

[eguthrie]

Hello. I've created a CRv11 report against data in an Oracle view. The report contains a dynamic parameter which uses a select query in a Crystal Command Object to provide a list of parameter values. Then I published the report to the Crystal Server.

We want the report query to be run by the user that is logged in (and not by the Oracle databse schema owner, which is "SW_MAIN."), so I modified the database configuration in the Central Management Console to use a custom database login: I changed the login information to my own oracle login and password, and then set the Table Prefix to "SW_MAIN." so that the data for the correct database schema would be selected.

When I run the report, I get an error that it can't find the datasource. I believe the problem is because the table prefix is not applied to the parameter's query at runtime.

If I remove the dynamic parameter, the report runs without error. So apparently the table prefix does get used for the main report query, but does not get applied in the case of the dynamic parameter query.

I tried replacing the parameter query in the command object by using a view instead, but I have the same problem.

Can anyone advise me on how I can fix this issue?

Thanks in advance,
Evelyn

 

[Turkbear]

Hi,
Create a synonym for the parameter's query target and grant that user read access to it - no prefix would be needed in that case.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[eguthrie]

Hi Turkbear,
I'm not sure what you mean by that. Can you explain it?
Thanks.
Evelyn

 

[Turkbear]

Hi,
In Oracle you can create alternate names for tables and Views that are simpler then the fully-qualified actual names ( like Schema.TableName) so for say SCOTT.EMP
you could enter:
Create synonym EMP for SCOTT.EMP;

See the following links for more info:

http://www.techonthenet.com/oracle/synonyms.php

or

http://www.dba-oracle.com/concepts/synonyms.htm

or

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref889

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[eguthrie]

Turkbear,
Thank you for your input, but I spoke to our head architect, and for reasons that haven't been really explained to me, we are not able to use synonyms.

So I still need to know if there is a way to implement the custom table prefix on dynamic parameters.

Thanks.
Evelyn

 

[Turkbear]

Hi,
I hate admins like that, at least there should be a reason.

Instead, try recreating the parameter's data table in the user's schema that is logging in.





To Paraphrase:"The Help you get is proportional to the Help you give.."