Use Crystal Reports to update a database

Updating a database from within a reporting tool is not always a good idea. But if you have a good reason to do it, you can.

By Lynn Evans

Crystal Reports is essentially a read-only tool. Its main role is to extract information from a database - not to update it. But, with a little effort, it is possible to make changes to your data from within Crystal Reports, as this article will show.

Just because you can, doesn't mean you should

Before we start, a word of warning. Using a reporting tool to update a database is not usually a sensible idea. Database updates need to be carefully controlled, with proper integrity checks and validation. In most cases, you won't want end users to be able to make changes to your vital corporate data simply by running a report.

That said, there might be times when it's desirable to write to a database as part of a reporting task. One example is where you need to record the date and time that a report is run. The database might include a table that stores that information, with a new record being inserted each time the report is executed.

Broadly speaking, there are three ways in which you can give Crystal Reports the ability to update a database.

User function libraries

The first option is to use CR's user function library (UFL) feature. A UFL is a collection of functions, written in a programming language such as Visual Basic, C++, C# or Visual FoxPro, and built into a COM server. Once you have created a UFL, the functions appear in Crystal's Formula Editor, and can be used in the same way as the built-in functions.

Clearly, any functions you create in this way would be capable of performing any task that's supported by your chosen programming language - and that includes updating a database (subject to the security imposed by the database and the operating system, of course).

So if you wanted to update a table every time a report was run, you would first write a UFL function that performs the necessary update. You build the function into a COM server (as a DLL) and distribute it with the report. In the report itself, you call the function from within a Crystal formula (you can pass parameters to it if necessary). You would then place the formula in, say, the report's header band so that it is called each time the report was executed.

The mechanics for creating a UFL are beyond the scope of this article. But the process will be straightforward for anyone who knows how to use the relevant programming language. Refer to the Crystal documentation for information about how to integrate your UFL with CR.

SQL commands

The second approach is to use SQL to handle the updating of the database. This method has the advantage of not requiring any external components to be distributed, although it does demand at least a basic knowledge of SQL.

In another Hex Central article (Use SQL commands to solve report problems and speed Crystal Reports), Mike Lewis explains how you can arrange for CR to send a SQL SELECT statement to the database as a way of obtaining the data for the report. In fact, you're not limited to a SELECT statement when using SQL commands. The same mechanism can be used to send INSERT, UPDATE or DELETE statements.

If you take this route, you will still need to obtain the data for the report. One way to do that is to place the INSERT, UPDATE or DELETE statements in the same SQL command as the SELECT statement. However, not all databases will allow you to combine SQL statements in this way.

An alternative method would be to use the SQL command for updating the database, and use a regular table or view as the data source for the report, just as you would if you weren't using SQL commands. In some cases, though, you might still need to provide a dummy SELECT statement within the SQL command, as CR will expect some data to be returned from the command, even if that data wasn't used in the report.

Keep in mind that SQL commands are executed as soon as you save them. This means that the update will take place, not just when you run the report, but also every time you edit and save the command.

For more information about how to set up a SQL command, see the article mentioned above.

Generating a script

The final approach to updating a database is somewhat different. Instead of the updating being done within the report, you use CR to generate a script. The script contains the required INSERT, UPDATE and DELETE statements. You run the script independently of CR (for example, in SQL Server's Management Studio).

This could be a good approach if you want to transfer data to a different table or database. An end user could Crystal's experts and wizards to filter and sort the data. But instead of showing the data in the usual tabular format, the report consists of a series of INSERT statements - one for each row - incorporating the values to be inserted.

The end user then exports the report to a text file. The result is a script that can be executed as is, or, at worst, with a little hand-editing and clean-up.

Although this method is not as useful as the other two, it is easier to implement. It also has the advantage of avoiding the issues of database permissions and security, as the updates are run within the context of the database's own tools rather than Crystal Reports.

Whichever of these methods you adopt, be sure to use it carefully. And please keep in mind my earlier remarks about not allowing end users to update your corporate data without proper safeguards and controls.

March 2012

Please note: The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly with your sites or applications.

If you found this article useful, please tell your friends: