Coding and Using Functions

Chapter 4 - Adding Business Logic with the Formula Workshop
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Okay, you’ve been reading about a good many of the syntax rules associated with Crystal Syntax and Basic Syntax and have identified some of the operator and function differences. Crystal supports the use of two kinds of functions:

• Custom functions

• Built-in functions

Functions can be written in either Crystal Syntax or Basic Syntax. As with operators and control structures, there are some syntactic differences between the functions in Crystal Syntax and those in Basic Syntax. One of the key differences is in how a value is returned to the calling function or the report in the two languages. In Crystal Syntax, returning a value requires only that the name of the variable that contains the value be the last line of code in the formula. In Basic Syntax, the keyword formula is used to return a value. In both of the following examples, a value that represents a 25 percent increase in unit price is returned to the report:

Crystal Syntax:

{Owners.UnitPrice} * 1.25

Basic Syntax:

formula = {Owners.UnitPrice} * 1.25 

One of the best ways to truly absorb the differences between the two formula languages and learn how to use functions is to code a formula or a function in one language syntax and then convert it to the other language syntax. We’ll begin our coverage of functions by building a custom function that will make use of some built-in functions. We’ll finish with a look at the built-in functions in Crystal from the perspectives of what functions are available, what kinds of control they offer, and when they should be used.

Crystal Custom Functions

Custom functions are modules of code that can be called by name and that can be passed parameters. These are functions are coded once in a report in either Crystal Syntax or Basic Syntax and used over and over again. In addition to being stored in a report and reused throughout the report, custom functions can be added to the Crystal Repository. This gives them two additional important features:

• The ability to share functions across many reports

• One central place to make code changes in shared functions

You can write your own custom function and store it in the report or in the repository. You can also use over 25 custom functions that ship with Crystal Reports 9 that you’ll find ready-to-use in the repository. You can even customize the custom functions that ship with Crystal Reports.

Coding a Custom Function

Are you ready to try your hand at coding and using a custom function?

Business Question: VistaNations wants to add a tag line to the bottom of every report to display the date it was printed using the general format “This report was printed on the 3rd day of September.”

To solve this business question, we could start with the special PrintDate field available from the Document Properties category of the Function Tree or from the Special Fields area of the Field Explorer. However, there are no functions built into Crystal Reports that will add a text-based suffix to a number or a date, so we’ll need to code our own.

The first step in writing any code is to think about the problem and come up with a solution as if you were going to solve it by hand without a computer just using your good old brain. Remember, the only reason we write code is to solve a problem. A solution to a problem is called an algorithm in programming terminology.

Designing the Algorithm

For the problem we’re trying to solve, the first step is to identify what the suffix will be for each number in a month. Our function is a formatting function. Here are the results we need to create:

1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th,

11th, 12th, 13th, 14th, 15th, 16th, 17th, 18th, 19th, 20th,

21st, 22nd, 23rd, 24th, 25th, 26th, 27th, 28th, 29th, 30th

31st

The next step is to look for and identify any patterns that exist. The way the numbers are arranged on this page makes it easy to pick out a few rules or patterns:

Numbers that end in 1 use “st” except for the teens.

Numbers that end in 2 use “nd” except for the teens.

Numbers that end in 3 use “rd” except for the teens.

Numbers in the teens use “th.”

Numbers that end in 4, 5, 6, 7, 8, 9, or 0 use “th.”

Were you able to identify all these patterns? Did you find others? These patterns represent the business logic we’ll code into our custom function. Let’s call the function AddSuffixToNumber, and begin the coding.

Coding the Algorithm

To begin, open the Formula Workshop by clicking its toolbar icon. When the Workshop Tree displays, highlight the Report Custom Functions category in the Workshop Tree and click the New button, as shown in Figure 4.32. We’ll build and test this function in the report and send it to the repository after we’re sure it works correctly.

Figure 4.32. Starting a new custom function

When the Custom Function Name dialog displays, type the name of the new function, as shown in Figure 4.33.

Figure 4.33. Naming a custom function

At this point, you have the choice of using the Extractor or going directly to the Formula Editor. Since we are not basing this new custom function on any other function, we’ll go directly to the Formula Editor to code our algorithm by clicking the Use Editor button. The Workshop Tree and the Custom Function Editor display; notice in Figure 4.34 that the new function has been added to the Report Custom Functions category in the Workshop Tree. The function name is highlighted and the typing cursor is positioned in the bottom editing window. The keyword Function ( ) is provided, and you can begin typing your code.

Figure 4.34. A new function in the tree

Note 

The Workshop Tree will display in whatever size and format (docked or floating) that you left it last.

Let’s break the writing of the function down into smaller steps for identification and clarity:

1. Name the function, identify the arguments, and set the return type.

2. Declare all temporary variables and assign data types.

3. Convert the number passed to the function to a string value.

4. Test the length of the string value; if the length is greater than or equal to 2, set the last two characters aside in their own variable; if the length is less than 2, prefix the number with a 0 in the two-character temporary variable.

5. Inspect the last two characters and for each pair, determine whether to suffix it with st, nd, rd, or th.

6. Return a string value as the last step of the function.

Figure 4.35 shows the completed code using Basic Syntax, while Figure 4.36 shows the same function written in Crystal Syntax. See if you can identify the built-in functions and operators being used as well as the control structures.

Figure 4.35. AddSuffixToNumber function in Basic Syntax

Figure 4.36. AddSuffixToNumber function in Crystal Syntax

The following language features were used in this custom function:

Built-in functions:

• ToText: Truncates a number to zero decimals and converts to a text string; this is a very useful function when presenting number data within text strings.

• Length: Returns the number of characters in a text string.

• Right: Returns the rightmost part of the string for the number of spaces specified.

Control structures:

• If-Then-Else

• Select

Operators:

• Assignment

While most built-in functions, control structures, and operators can be used in custom functions, there are a few limitations imposed because of the need to guarantee reusability across a report and, if added to the repository, across multiple reports. When coding custom functions, avoid using the following in the body of the function:

• Built-in functions in the Evaluation Time, Print State, or Document Properties categories

• Calls to user function libraries

• Database fields

• Formula fields

• Global variables

• Recursive function calls (functions that call themselves)

• Shared variables

• Summary fields

Setting Custom Function Properties

The Toggle Properties display button at the top of the Formula Workshop is used only when working on a custom function. Use the button to display the Formula Editor and the code for the custom function or toggle it to display and edit the properties for the function. Figure 4.37 shows the properties for the AddSuffixToNumber function.

Figure 4.37. Custom function properties

The text typed in the Category area displays in both the repository and the Function Tree, which is shown in Figure 4.38. If you discipline yourself to using a few meaningful categories, this text can help you easily find the functions later on.

Figure 4.38. Categorized custom functions

The Help Text button visible in Figure 4.37 opens a window for free-form typing. Use this area to type helpful hints and how-to steps for using the custom function.

Using the Formula Extractor

You can create a custom function based on any existing formula. The Extractor tool pulls out the executable code and replaces key values with variables and arguments. The arguments are generically named (v1, v2, v3…) and can be directly renamed to more meaningful names. After extracting a custom function, you must save it with a new name before using it in the report. The option to use the Extractor appears when you are creating a new function; it is a button choice in the dialog shown previously in Figure 4.33.

Calling a Custom Function

Calling a custom function is identical to calling a built-in function: Use the function name and provide values for the arguments to the function as well as a place to use the value returned from the function. One of the easiest ways to do this is to create a formula field, double-click the function name in the Custom Functions category to add it to the code editing window, and type a valid value for the argument. In Figure 4.39, a call is being made to the AddSuffixToNumber custom function through the formula field callAddSuffixToNumber, passing in the integer value 13; the expected result of the function is the string 13th. To see this value in a report, drag and drop the formula field callAddSuffixToNumber onto your report and then preview it.

Figure 4.39. Calling a custom function

So, to summarize, calling any function requires the following steps:

1. Type the name of the function.

2. Provide valid arguments to the function.

3. Use the return value.

Storing a Custom Function in the Repository

Adding a report custom function to the repository is an easy matter of clicking an icon in the Formula Workshop. With the function displayed in the Formula Workshop, click the Add To Repository toolbar icon, or right-click the formula name in the Workshop Tree and choose Add To Repository from the submenu that appears.

Using Crystal’s Custom Functions

The Crystal Decisions team took a look at the most popular User Function Libraries (UFLs) in use for Crystal Reports 8.5 earlier and came up with four groups of custom functions that ship with the Crystal Repository. These functions may replace some of the UFLs in use in your organization. The groups are date functions, financial functions, formatting functions, geographic functions, and math functions. Each of the functions is prefixed with the characters cd to identify them as Crystal Decisions functions. You can use these functions as a starting point and customize their code to your needs. To customize a function in the repository, you must first disconnect it from the repository and then add it back to the repository after making your changes. Refer to Chapter 5 for more information on the repository.

Refer to Appendix D, “Crystal Reports Custom Functions,” for a complete list and description of custom functions.

User Function Libraries

Custom functions in Crystal Reports may replace many user-defined function libraries in use in prior versions of Crystal Reports. UFLs are code routines typically written in languages such as C, C++, and Visual Basic and compiled into dynamic link libraries (DLLs).

Crystal Reports ships with several UFLs that you’ll find listed under the Additional Functions category of the Function Tree. When deploying a report that uses UFLs, you need to distribute the DLL file that represents the UFL in addition to the RPT file for the report. The UFL DLL files can be found in the system folder C:\Program Files\Common Files\Crystal Decisions\2.0\bin.

Because UFLs require the distribution of a DLL in addition to the RPT that calls the function, custom functions and the Crystal Repository might provide an easier reusability path and distribution path as they are contained within an RPT file.

Crystal Built-in Functions

In building our custom function, we used Crystal Build in function. Please refer back to Figure 4.34 and notice the Functions tree. Crystal Reports provides an enormous number of built-in functions to help make writing formulas easier. The Function Tree splits the functions into categories based on data type and usage type. If you need to do mathematical calculation, refer to the math branch for available functions. If you need to work with dates or strings (characters), refer to the appropriate function branch. As with any long list of function you only need to know about twenty percent of the functions and this will cover eighty percent of your work—the rest you can learn about as the need arises. All the functions are called using the same syntax discussed in this chapter, so all you need to do is familiarize yourself with the groups and functions available in the groups to be able to build custom business logic for your reports.

Refer to Appendix E, “Crystal Reports Built-in Functions,” for a complete list and description of the built-in functions.

Note 

Refer to the help file for the calling syntax and return type associated with built-in functions.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback