Formulas are used in Crystal Reports for enhancing report functionality as it prints. For example, Tutorial 5-1 showed how to use a formula to have the rows on the report alternate colors. Another example is suppressing a row by comparing a field against its current value.
Crystal Reports gives you the option to program formulas in either Crystal syntax or Basic syntax. The default programming language is Crystal syntax and that is what this chapter focuses on. Basic syntax is covered in Appendix B.
This chapter gives an overview of programming Crystal syntax and focuses on areas that are not as intuitive and that require more explanation. When I say, “intuitive”, I mean this from the standpoint of someone already familiar with how to program. If you have never programmed before, then this chapter might be a bit intimidating at first time. I try to give enough examples so that you can see how each concept is used in practice, but if you really want to create detailed formulas then you might be best off reading a book dedicated to teaching you how to write programs.
Formula Fundamentals
To learn how to program with Crystal syntax, you want to start with the fundamental tasks that might seem pretty mundane. There are certain aspects that you need to know even if you only plan on writing some very simple formulas. By understanding the basic structure of a formula, you can build on this knowledge to create more complex logic. This section covers the fundamentals of writing formulas.
Case Sensitivity
When a language is case sensitive, it considers identical words to be different when the letters use a different case. Crystal syntax is not case sensitive. For example, the variable FirstName is the same as the variable firstname. Although these two variables are syntactically equivalent and can be used interchangeably, it is recommended that you keep the case consistent so that your program is easier to read.
Writing Comments
Comments let you add notes to your code so that it is easier to understand. You can explain why you did something or add clarity to the purpose of the formula. It is common to write a formula and think that its functionality is so obvious that there is no reason to write comments. But six months later you have to modify the report and when you look at the formula you have no idea why you wrote it the way you did. Adding comments ensures that at a future date you’ll understand why you did something. If someone else comes along later to review your report, then they will be thankful for the comments.
Crystal syntax uses // to designate a comment. It can be at the beginning of a line or in the middle. Anything after it is ignored.
Line Terminators
When a formula has more than one line of code, you need some way to mark the end of the line. In some programming languages each programming statement has to be on a new line. Crystal syntax allows more than one statement on the same line. A semi-colon is used as the separator between statements. Even if each statement is on a new line, you still need to put the semi-colon at the end.
If the whole formula only has one line of code, no semicolon is needed (but you can put it there if you wish).
Returning a Value
Formulas are generally used to calculate a value that gets displayed on a report. This could be someone’s average salary or concatenating a first name and last name into a single string value. Consequently, Crystal Reports requires every formula to return a value.
If a formula doesn’t specifically return a value, then Crystal Reports assigns the Null value to it and defines it as a string data type. There are times when this can actually be useful. For example, if you need to link to a subreport using a field with a null value then you can create an empty formula and link it to the subreport.
Crystal Reports uses the value of the last line executed in a formula as the return value. Any value on the last line will be returned. Here are a few examples. This formula returns the number 5.
The next formula assigns the result of a calculation to a variable. That result is returned by the formula.
In the next example, the last line executed is what returns the value. The If-Then statement has two exit points and the one chosen is dependent upon the condition.
When you have a statement that can return multiple values, make sure that each one is of the same data type. Crystal Reports will check all the values and give you an error if they don’t match up.
Referencing Report Fields
Writing formulas requires referencing all types of data from your report and the tables that the report uses. The types of data that can be referenced consist of running totals, functions, formulas, and table fields. The syntax for referencing a field is to put curly brackets around it. In addition to that, each type of field has a special character which designates its type. Some examples follow.
Formulas are referenced by putting @ in front of their name.
Parameters are referenced with a ? in front of the name.
Running total fields are referenced by putting # in front of the name.
Table fields are referenced by separating the table name and the field name with a period between the two. Spaces are allowed.
Group fields use the field name with the GroupName() formula.
Summary fields pass the field name and the group field as parameters to the summary function.
