Crystalkeen logo; we offer Crystal reports viewers and schedulers
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

Best sellers:



CR Schedulers
CR Viewers
DataBase Tools
Mail UFLs


About us

Contact Us
cViewSUITE Ppt


on Steroids

Crystal Reports Basics: Using Formulas and Functions

This is based on the book, Crystal Reports: A Beginner’s Guide.

To compare, analyze, and derive information, you often must use formulas. A simple example is this. Suppose you have the sales figures and want to know which sales people met quota, which exceeded quota by more than 10%, and which missed quota by more than 10%.

You would write three formulas: one showing sales=quota, a second showing sales >= (quota * 1.1), and a third showing sales <= (quota * 0.9). You would then combine these into a nested arrangement of if/then statements so one formula gives you the results.

Most of your formulas are likely to be simple arithmetic formulas (+, -, *, /). Crystal Reports makes those pretty easy—you just select what you need from the Formula Editor. For more complicated formulas, you would need to use Crystal Reports Functions.

Crystal Reports contains over 200 functions, so it can be confusing to figure out which one you need. Crystal Reports has an online help function to aid you in sorting this out, so don’t worry.

Among the functions are the "Summary Functions": Sum, Avg, Min, Max, Count, and Distinct Count.

String Functions allow you to perform "concatenation," which is a fancy way of saying they allow you to combine two or more string fields. For example, you may want to convert database-ese to English when it comes to providing a list of customers. So, you can concatenate the Customer.FirstName field and the Customer.LastName field.

Type-Conversion Functions allow you to import data of an inappropriate type to the one you need. For example, you may have sales dollars entered as text in the original database. Rather than modify that database and covert those a currency type, you let a Function do the work. Now, you can do calculations without changing the source data. You can change text to number, number to text, and so on—including such field types as Boolean, currency, date/time, integer, and string.

Period Functions and Date Fields are basically predefined periods, such as 90 days. If you want to alert you’re A/R department about invoices that are 90 days overdue, Crystal Reports will check today’s date and do the math for you. It will produce a list of all affected items.

Earlier, we mentioned If/Then statements. Crystal Reports is similar to BASIC, in that it lets you write If…Then…Else statements, so you can add logic to your formulas. These statements work this way: If X is true, then do Y. Otherwise, do Z. For X, you can use a calculation or simply refer to raw data. The action, Y, can also be a calculation. Or, it can be a change in formatting. Or, it can be an action like adding data to a list, incrementing a formula, changing a status code, and so on.

As with all formulas, syntax is critical. In Crystal Reports, the order of operations is conventional—parentheses, exponents, multiplication, division, addition, then subtraction. A good practice is to put all operations in parentheses—just don’t forget to use matched pairs or you will get undesirable results. And don’t forget that function parameters also require their own set of parentheses.

Fortunately, Crystal Reports has a syntax checker built right into it. Still, you may have a hard time tracking exactly where the error is and how to fix it. So, write out any nested formulas on paper, and use different colors, different rows, or some other method to differentiate between the nested "sub-formulas." This is much quicker than trying to fix a formula once you’ve written it.

For even more functionality, you can use third-party programs, such as the ones available here.


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.


Except where an author's name is given at the start of the article, all of these articles were written by Mo Naughton or Bruce Ferguson and edited by Mark Lamendola. Mo is a Crystal Reports consultant, trainer, and developer for Chelsea Technologies, Inc. Bruce Ferguson is a Crystal Reports consultant, trainer, and developer for CrystalKiwi, Inc. Mark Lamendola is a writer and editor with over 15 years experience in professional and trade publications.