Change SQL statement

[Former Member]

Hi,

I would like to manually change the SQL statement that Crystal Reports 2008 generates (I am using several Oracle tables and views in my reports).

Where is this possible? I can show the SQL statement in the database menu but can't modify it there.

Thanks!

[Former Member · Accepted solution]

hi,

Go to Database ->Database expert. On Right hand side, under Selected Tables,

select the data provider and right click, you will see the option "Edit Command".

Regards,

Vamsee

[Former Member · Accepted solution]

Hi,

unfortunately there is no "Edit command" function. There is no context menu for the data provider - only for the tables, where I can see the properties and can rename them...

[Former Member · Accepted solution]

You can not modify SQL generated by Crystal.

You can copy the Crystal SQL and then create a new report using a command.

Ian

[Former Member · Accepted solution]

hi,

The steps i mentioned are relevant if you are using 'Universe' as a datasource.

As Ian said, you can't modify the SQLcommand generated by Crystal Report.

Regards,

Vamsee

[Former Member · Accepted solution]

Ok, thanks for the information! In earlier versions of Crystal Reports it was possible to modify the generated SQL.

Hmmmm.... what can I do instead?

[former_member11696 · Accepted solution]

Hi Steve,

After CR 8.5 yo can no longer edit the SQL directly. What we did was to create a Command Object which allows you to paste in or write your SQL. The REprots then use it as the data source.

As Ian suggested, open up your existing reports, copy the SQL from the Show SQL menu option. Create a new report and in the Database Wizard you'll see a Command. Select it, paste in the SQL and then copy the Objects from the old report into the new report.

Unfortunately there is no Set Location option to go from a report based on a direct connection to a Command object. Only way is to recreate your reports.

Thank you

Don

[Former Member · Accepted solution]

Hi,

Something we use (for other reporting systems) is a view. In the veiw we create our optimal sql then just point the report to the view. It requires a little coordination with the DBA but if it saves a few minutes on the report then it is worth it.