Crystal Reports: Custom Functions in Crystal Reports

	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports: Custom Functions

Using Crystal Reports 9 Custom Functions

Custom functions enable you to create a general function and use this in several formulas in your report. In this example we need to develop a custom function to calculate the last day of the previous month for the date we pass as an argument to the custom function.

In the formula workshop, select “New/Custom Function” and give your custom function a name:
(LastDayPrevMonth).

The custom function in Crystal Syntax will look like:

Function (DateTimeVar dayInMonth)
Date (Year (dayInMonth), Month (dayInMonth), 1) – 1

Then in your report formulas, you can call this custom function for any datefield :
LastDayPrevMonth({table.datefield})

Custom functions cannot use shared or global variables, or database fields, although you can pass these into the custom function as arguments to the function.

You cannot use Print State, Document Properties, User Function Library and some internal functions like Rnd, CurrentFieldValue, DefaultAttribute or GridRowColumnValue.

This raises the interesting point that while you cannot use “PrintDate” you can use: “CurrentDate” in the calculations inside your function.

Custom Functions can return an array or range. You can leave your custom function in your report, or save it to the repository and use it in several reports.

In November 2001, we published Charliy Nash’s excellent formula for accurately calculating a person’s age as at any date. Charliy has now sent us the formula as a Custom Function. Place this in the repository to have the function available in all your Crystal reports:

Function (datetimevar Birth, datetimevar Current) if Month(Birth) < Month(Current) or (Month(Birth) = Month(Current) and Day(Birth) <= Day(Current)) then year(Current) - year(Birth) else year(Current) - year(Birth) – 1

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.

Consider UFLs, also

What is a User Function Library (UFL)? Essentially, it's a piece of code that plugs into Crystal Reports to expand its functionality. Most people do not need every UFL available. But, nearly everyone needs at least one or two UFLs.

What does it take to write a good UFL? Here are some of the requirements:

A solid knowledge of a programming language such as Visual Basic.
A solid understanding of how to write DLLs.
A solid understanding of how to write DLLs that "play nice" with other programs.
An understanding of the Crystal Reports Equations Editor and Function Tree.
Knowledge of the file-naming conventions that allow UFLs to work properly and to cohabitate with other coding.
Knowledge of how to declare and create items inside the class modules.
Understanding of, and adherence to, Crystal Reports' UFL deployment requirements.
The ability to test UFLs on various machines and across various test situations--both simulated and real.
The ability to update the UFL when updating Crystal Reports.
The time and patience to develop the UFL methodically, streamline its code, and test it properly.

Now, don't worry if you don't have all of requisite skills and resources. We have all of these and more, and thus they can fill the void for you (see below).

But where does a UFL go?

User Function Libraries are used inside a formula to add calculations that are not otherwise available. They show up as Functions in your Formula Editor and extra functions are usually displayed under Additional Functions.

We have developed some interesting functions, and there are some others available from Business Objects and other 3^rdparty developers.

User Function Libraries first appeared in Crystal Reports 5, and were a DLL that was installed in the Windows\Crystal folder. This folder continues to work for all subsequent versions of Crystal Reports.

You can also install them in the Crystal Program folder. This varies from version to version since version 9.0. Crystal Reports 2008 installs into

C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\win32_x86

Some great UFLs

Below is a listing of some great UFLs. Click the link or associated image for pricing and information on each one.

XML UFL for Crystal Reports
cViewIMAGE
cViewPICK
Registry Function Library for Crystal Reports
International Function Library for Crystal Reports
Disk File Management Function Library
Text Lookup Function Library for Crystal Reports

Intrigued? Here's some info on the GRID UFL.

Export structured data to Excel or another application, the smart way. Grid UFL provides you the proper control of your Crystal Reports Export. The ability to export your reports to another application or format is one of the really useful features of Crystal Reports. But when you do this, you run into the limitations of both Crystal Reports and the other application. Grid UFL helps you overcome those limitations.
A really useful UFL:

Grid UFL allows you to treat your saved data as a Grid. To do this, you use the GridCellClear (rows,cols) to declare a work area of a predetermined size. This formula should normally be in your report header section. Then use other formulas in your group and details section to save values in the grid.

You can use GridCellLoadRawValue (row, col, value) to store a value in the working area. You could also use GridCellLoadDelimitedValue(row, col, value, delimiter) to automatically put quote marks or some other field delimiter around the value.

You can save it all to the disk file with GridCellSave(Filename,Separator). Use a comma, tab character or any other ASCII value to separate each value on each line of your disk file.

Think of the possibilities. Grid UFL opens an enormous number of them.