Updating a database table from a Crystal Reports formula

Friday 17 July 2020 @ 8:44 pm

One of the things that makes CR such a powerful and flexible tool is the ‘ecosystem’ of third party tools that has grown up around Crystal Reports. I was reminded of this last week as I worked on a project using the Cut Light UFL by Millet Software. The Cut Light UFL allows your formulas to do more than simply add columns to a report. It includes 130 additional functions that let you interact with the operating system, other applications and even SQL databases (among other things).

In this case I was helping a customer create a report that reads data from one database, does some complex calculations and then writes the results of the calculations to a table in a data warehouse. The function we were using lets our formula launch a SQL INSERT statement to add a row to the table. We had done this before with smaller tables, but this time the INSERT involved a table with dozens of fields, and we ran into a limitation on the size of the query that Cut Light could process. I asked Millet software about the limitation and within 2 hours we had an updated UFL that could handle a SQL statement up to 250K characters.

If you want to learn more about giving superpowers to your formulas, you can see my annual review of User Function Libraries (UFLs). If you want to get an overview of ALL of the third party products available for Crystal Reports, you can see my product LINKS page.

Leave a Reply

Recrystallize Pro