Custom functions in Crystal Reports

Saturday 23 April 2016 @ 12:37 pm

Crystal Reports introduced custom functions back in version 9, but I have only recently started using them in production. To make them worthwhile you have to have reports that do the same type of calculation many times but with different fields. You also have to be willing to move the function from one report and add it to another whenever you need it in a different report.

But I have one very active customer who needs very complex reports with hundreds of formulas. In these reports we found ourselves doing the same two things over and over again:
1) Calculating an elapsed time number (seconds or minutes) and then converting that into a string (hh:mm).
2) Converting a time string (hh:mm) back into a number for use in other calculations.

After writing dozens of similar formulas (and sometimes forgetting an important piece of the logic) we decided to create two custom functions. So here are the two examples of the custom functions described above. You can paste them into your reports if you have a use for them:

//SecondsToTimeString:
Function (numberVar TotalSec)
NumberVar Hours := Truncate (Abs( TotalSec / 3600)) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
(if Totalsec < 0 then '-' else '') +
Totext ( Hours , '00' ) + ':' +
Totext ( Abs(Minutes) , '00' )

//TimeStringToMinutes
Function (StringVar TimeString)
Val (TimeString ) *60
+ (Val (Right (TimeString,2)) * (if TimeString startswith '-' then -1 else 1) )

To use one of these functions you first have to add them as “Report Custom Functions”. You will find these within the formula editor, just above the list of formulas in the side panel. You right click on the category, select NEW and give the new function a name. Then you paste in one of the functions above, save and close. Once they are saved you can edit any formula field to confirm that it is in the function list. There should be a new category of functions called “Custom Functions” and the new function should be listed.

These new functions can be used in any formula. The first needs a number argument (in seconds). The second needs a string formatted as a positive or negative time string (hh:mm or -hh:mm).  For example:

TimeStringtoMinutes ( {String.Field} )

SecondsToTimeSTring ({Number.Field})

Just remember that when you add a custom function like this it adds the function to the one report that is open. If you want to use it in other reports you have to create the function in each of those reports. The only way to make a function available to all reports in your environment is to compile the function as a DLL and place it in one of Crystal’s library folders. These are called a UFLs or User Function Libraries. There are several of these libraries for sale and some that are free. These are listed and explained in my annual review of UFLs.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Recrystallize Pro