Crystal Reports: IIF Function Isn't IF

Conditional formulas with the If..then.. else control structure have been widely used in Crystal for many versions now. Excel experts will also be familiar with the IF() function they use there to apply similar logic and they might discover the IIF function looks like it does the same thing.

But it doesn’t.

IF {table.field2} = 0 then 0 else {table.field1} / {table.field2}

IIF ({table.field2} = 0 , 0 , {table.field1} / {table.field2})

Do NOT do the same things. Because the IIF function is a FUNCTION it has to calculate both arguments before it decides which one to select. So if field2 is zero, the formula will fail with an error. The division part of the IIF is always calculated so you can’t use an IIF to avoid a divide by zero error.

In a similar way, if either argument of the IIF is used to accumulate a variable then it will ALWAYS accumulate the variable. Use the IF THEN ELSE control structure to calculate the results correctly.

Remember, IIF is a function. IF is a conditional statement.

While we're on the topic of functions, consider using User Function Libraries to add great functionality to your Crystal Reports system.

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^rd party 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 very useful 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.