Crystal Reports: Generate SQL to Change Selected Records

Use CR to generate SQL to change selected records.

The database was wrong. We could see it had incorrect values for some of the records. We could write a Crystal Report to find the errors and even to calculate the correct value to put back in the field. So why not use CR to generate the SQL we needed for the update?

Use the error report as the foundation for a new report. Remove all fields and create a new formula that looks like:

“UPDATE myTable SET ThisField=’” + {@NewValue} + “’ WHERE RecordID=” + {Table.RecordID} 

The report is now a long list of SQL UPDATE commands. Export the Report to a Text File and then use the Text in a SQL Query tool. This can update hundred of records with a few mouse clicks.

Sometimes ,we receive a request to develop a report to change data in a database. We can see several ways to do this, but we don’t recommend you use any of those methods.

We believe that every report should be able to be reprinted and reprocessed using the same data and getting the same results. If a report changes that database during processing, it is a volatile report and reruns cannot be handled in the usual way.

For this reason, we have not developed a User Function Library (UFL) that can update SQL databases. There are UFLs that do this, but we prefer a more robust and repeatable production or ad-hoc solution. We have developed a better way to combine the two steps of a report and a database modification.

Earlier, we suggested you use a report to build a SQL Update statement, and then export this to a text file. Then paste the text output into a SQL tool and apply the update your database in a second step.

You can now automate this by combining our Disk Output UFL with our cViewSQL utility. Use a formula to build the SQL Updates you need in your database. Then use cViewSQL to process these lines. Breaking the task into two steps makes it a lot easier to develop, test, and maintain.

Use SQL commands to retrieve your data.

A great feature introduced in Crystal Reports 9.0 is the SQL command, which you can use to retrieve your data.

Sometimes we need to manipulate our data with SQL prior to processing a report.

For example, we combine a SELECT DISTINCT, UNION join and Sub SELECT to retrieve some data from our SQL server.

The SQL command can be added to the repository, so you can share it between several reports. If you wish to change your SQL, you need to detach the SQL from the repository, make your changes and then place it back in the repository.

The SQL Command feature is why Crystal Reports 9 does not include the Crystal SQL designer. You can do far more with Crystal Reports SQL commands. 

SQL command parameters

Use SQL command parameters. Using SQL commands as the source of data for your report has several benefits. This SQL command feature also includes the ability to include a parameter in the SQL command.

For example, we have created a parameter {?Region} and use that to select a single region 
from our table.

The SQL command now says Select * from Customer where Region = '{?Region}' 

The parameter editor in the SQL command dialog box lets you enter only a single default value. So if you wish to load more default values, return to the Field Explorer and add more default values to the parameter there.

This feature supports string, date and numeric parameters, but they must be a single value. Multi value parameters are not supported.

The real benefit of this feature is where you need to extract a few records from a large table. Doing the selection in an SQL command will have a major improvement in report performance. 

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.