Can we modify SQL of reports created with SQL Commands?

[GMcNamara]

We recently converted to Crystal Reports 9 (from 8.5) and I updated over 100 reports using SQL Commands. This was not terribly difficult work but it was time consuming. We run our reports through a Delphi application that we use to change the SQL of the report at runtime, allowing our users to select certain parameters, append selection criteria to the SQL clause, etc. When we were unable to run our reports through our application, we were told by Crystal support that updating the SQL of a report that utilizes an SQL Command is impossible. We were told this was an 'undocumented limitation'.

Has anyone had a similar experience and could confirm or deny this to be absolute? We are using the RDC and are able to read the SQL, but have been unable to modify it. Suggestions??

 

[kenhamady]

I was not aware of this feature. But I can make a suggestion for a workaround. If you send your criteria as a Record Selection Formula, rather then directly to the WHERE clause, you will get the same end result. The performance may be a bit slower, because the SQL is returning all of the records in the original Command's WHERE clause, but the RSF will take the appropriate subset of those for the final report.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

I think you may have made a mistake by converting them, as Ken and CD suggests, you now cannot optimize the SQL.

Not to make your life anymore difficult...but I would convert these SQL statements to Views, and then as Ken suggests, pass the appropriate record selection formula.

In this scenario the SQL will be passed to the database, providing you correctly formulate the record selection formula.

-k

 

[chelseatech]

I can see a couple of solutions to this problem.

1) If the SQL Command is in the repository, use your code to find and update it there.

2) The SQL command can include a parameter so as an example
an SQL command of
SELECT * from Customer {?Where}
Will get all the records if the parameter is blank, and
will select only the USA customers if the parameter has the value..
WHERE Country='USA'

Potentially the field list and Group by could also be optional parameters. I'll leave the rest of the potential for that one to your imagination.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[GMcNamara]

Thank you all for your responses. As far as using the Record Selection or using parameters within the SQL Command, we are trying to avoid adding the extra tables to the report (most of the time, the tables that we are using for selection criteria are not included in the report). If we can't modify the SQL Command, it looks like our performance will take a big hit.
We upgraded from 8.5 because we could not modify the WHERE clause of the SQL query at runtime and also use the select expert for record selection. We thought that Crystal 9 would eliminate the problem by allowing us to use parameters directly in the SQL Commands. It looks like we are no better off then before.

 

[kenhamady]

Another option is using record sets generated by the application, but this usually means recreating the report from scratch, not just converting it.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[GMcNamara]

Thanks Ken. We were looking into that but were hoping we could find another way. It looks like that will be our only solution.

 

[Mack2]

Do I understand your question correctly, If you want to pass parameters using a command that can be done. Within the create command dialog box, select create parameter, after it is created double click it, then it will be added to your sql code. And paramteres can be sent from your application.

 

[GMcNamara]

Yes, we are creating parameters directly in our SQL Commands. However, these parameters will not accept range or multiple values.
Also, we are trying to select based on tables that are not contained within the report. For instance, we have a report dealing with Employees. At runtime, based on the user's parameter selection in our Delphi application, we would like to append to the SQL query: WHERE BRANCH.BRANCH_ID = 1 OR BRANCH.BRANCH_ID = 2, assuming the user selected Branches 1 and 2.

 

[Thadeus]

Not to get too micro in your macro problem, but couldn't you use an array for that issue specifically? WHERE BRANCH.BRANCH_ID in [{parameter}]

Thadeus

 

[GMcNamara]

Unfortunately not. If I was able to use the Select Expert, this would work. However, you have to choose between using the Select Expert, or modifying the SQL of the report. Also, the BRANCH table is not necessarily included in the report.
Second, the parameters created within the SQL Command won't accept multiple values. If anyone has found this to be inaccurate or knows a way that I can make a parameter created in an SQL Command accept multiple values, please let me know.

 

[GengisTom]

Yes,

You can edit Commands created in version 9.

All you have to do is choose database/database expert from the menu bar.

This will open the database expert dialog window. You will see your command in the "selected tables:" frame.

Just right click your command, and click "edit".

You can now edit your command.

If you have saved your command to the repository, the Edit option will not be enabled until you first select "Disconnect from Repository."

Hope this helps.

 

[GMcNamara]

Thanks again to everyone for responding. However, I am obviously horrible at explaining myself. I can edit the command from within Crystal without incident. The problem is editing the command at run-time through our Delphi application. The application should pull the SQL from the report, append certain WHERE clauses according to the user's selections, write the updated SQL back to the report, and then the report runs.
The good news is, it looks like we will be able to use record sets to achieve our goal. It has taken some time to discover even more of those 'undocumented limitations', but we are begining to see the light at the end of the tunnel.