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

  Resources  

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
 

CrystalReports
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.

Authorship

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.

 

  • Questions? Please write to mark@crystalkeen.com. We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to mark@crystalkeen.com and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.