Recent Improvements to Formulas

Because Crystal Reports has been around for so many years and has released many versions over those years, it's not uncommon to come across users who are still using older versions. To bring those users up to speed this section covers some of the recent additions and improvements to formulas over the past few versions.

Manipulating Memo Fields in Formulas

In the past, Crystal Reports developers had not been able to access string fields that were longer than 255 characters within the formula language other than to find out whether they were null. This limitation has been completely removed.

For our purposes here, let's assume that the Xtreme Mountain Bike Company management needs an HR report that shows only the female employees, but there is no gender field in the Xtreme database. In the Notes field in the Employee table, the word "she" is used for all female employees. However, Xtreme's management has indicated that they might need to search for other words as well, so they want to have a keyword search instead of hard-coding the search values. Follow these steps to create such a report to fulfill this reporting requirement:

1. Open the Employee Profile Report. Press Ctrl+O to open a report. Find the Crystal Reports 9 sample report called Employee Profile. Most installations have it in the following folder: Program FilesCrystal DecisionsCrystal Reports 10SamplesenReportsGeneral Business.
    
2. Create a parameter field by selecting View, Field Explorer. Right-click on the Parameter Field item in the Field Explorer and choose New. In the Create Parameter Field dialog, call the parameter Search-A-Word. Prompting Text should be What word would you like to search for?. The Value Type should be String.
    
3. Click on the Set Default Values button. In the Set Default Values dialog, add "", "she", and "he" to the default values. The final result should look like Figure 13.1.
    

   Figure 13.1. The default values for a Search-A-Word parameter.

    
4. Click the OK button. The Create Parameter Field dialog should look like Figure 13.2. Click OK.
    

   Figure 13.2. The parameter settings for Search-A-Word.

   

    
5. Connect the parameter to the Selection Formula. Select the Formula Workshop via Report, Formula Workshop. Then choose Selection Formulas, Record Selection from the Workshop tree. Enter the following selection formula into the editor: IF {?Search-A-Word} = "" THEN TRUE ELSE ({?Search-a-Word}) IN LowerCase({Employee.Notes}) and click the Save and Close button.
    
6. Run the report. When prompted, choose she from the Parameter Field prompt and choose to refresh the data. The end result is that only the female employees appear on the report as shown in Figure 13.3. Save the report as Chap13_1.rpt.
    

   Figure 13.3. The Employee Profile showing female employees only.

    

TIP

Notice that you only put the LowerCase() function call around the {Employee.Notes} field and not the parameter. This is because you put the values into the parameter as lowercase by default. However, because you allow the business users to input their own values into the parameter, it might be a good idea to put the LowerCase() function on the parameter as well. This allows Crystal to compare apples to apples when evaluating these exact values. Alternatively, both could have been set to UpperCase() as well.

A keyword search is just one example of how to use a memo field in a formula. The 255-character limit for formulas that was removed in version 9 of Crystal Reports means that practically all database field types can now be accessed in formulas and manipulated. Remember that memo fields are really just long string fields, so they are treated as strings in the formula language. Wherever a string can be called, now a memo field can be called as well.

CAUTION

Not all databases support the capability to search large string fields, so if this type of keyword search is required, more records than necessary might come across the network. For the preceding example, 15 records were returned from the data source but only the 6 that were female were shown on the report. This is because the data source couldn't be passed this selection criteria to handle on the server side.

It is a powerful new feature, but keep in mind that it might bring back more records than you expect.

 

Working with the Additional Financial Functions

In older versions of Crystal Reports, the financial functions capability of the formula language was limited to 13 functions. However, version 10 of Crystal Reports provides more than 50 financial functions. With overloads for parameters, these functions count up to about 200 variations.

These functions were implemented to give as much functionality as possible to a highly skilled group of report designers. In the past, they had to hand code the financial functions. By including the standard financial functions that most users have seen in Microsoft Excel, these report developers can now develop their formulas much more quickly.

For more information on the Financial Functions available, refer to the Crystal Reports Help file. In the Index, look up "Financial Functions" for a complete list of what is available.

Creating Custom Functions in Your Reports

Custom functions were introduced in Crystal Reports 9 and continue to be a powerful feature of Crystal Reports 10. Although they have been introduced in Chapter 4, "Understanding and Implementing Formulas," this section focuses on some more detailed information on what they are and how they could be used in report development.

Custom functions are packets of business logic that are written in Basic or Crystal syntax. These functions do not have any reference to any database fields at all. Because these functions contain logic that will change values and return a result, the values must be passed in and the results of the logic must be passed out or returned.

Only 10% of a custom function is different from your average formula. As mentioned previously, parameters must be passed in to allow for data manipulation because a custom function is stateless. This means that it has no meaning outside the function it has called in. It acts just like all the other formula functions in the formula language. The only difference is that custom functions can be created, edited, and deleted, whereas Crystal formula functions are completely unchangeable.

Here is a custom function that is provided within the sample repository that comes with Crystal Reports:

Function cdExpandRegionAbbreviation (regionAbbreviation _

 As String, Optional country As String = "USA")

 Select Case UCase (country)

 Case "CANADA"

 cdExpandRegionAbbreviation _

 = cdExpandRegionAbbreviationCanada (regionAbbreviation)

 Case "USA", "U.S.A.", "US", "U.S.", "UNITED STATES", _

 "UNITED STATES OF AMERICA"

 cdExpandRegionAbbreviation _

 = cdExpandRegionAbbreviationUSA (regionAbbreviation)

 Case Else

 cdExpandRegionAbbreviation = regionAbbreviation

 End Select

End Function

Some of the things you will notice about the preceding code are as follows:

• It's in Basic syntax. This is not a requirement of custom functions. They can be in either Basic or Crystal syntax.
• It does not reference database fields directly. Any information that is needed from database must be passed in via the parameters in the first statement (regionAbbreviation).
• It has an optional parameter (Optional country As String = "USA"). This means that this parameter does not necessarily need to be passed in for the function to work. If this parameter is not supplied by the developer in the formula, the value of "USA" is used by default.
• It calls other custom functions. CdExpandRegionAbbreviationCanada and cdExpandRegionAbbreviationUSA are also custom functions. In fact, they are Crystal syntax custom functions. (This shows that Basic and Crystal syntax can call one another.)
• It has a definite end-point (End Function). This allows for the final result (the functions return) to be passed back out to the formula making the call.

TIP

The Enter More Info button takes you to another dialog where you can enter much more descriptive text around the custom function. It also contains fields for categorization and authors. From there, you can also add help text via another dialog. For more information on these dialogs, consult the online help.

 

Sharing Custom Functions with Others

Two ways in which you can share custom functions are

• By using them in multiple places in one. Because custom functions are stateless, different parameters can be passed in to allow for instant function reuse.
• By sharing them in the Crystal Repository. Custom functions are one of four report object types that can be shared in the repository.

Custom functions can be used in many ways. Take your existing formulas, convert them, and share their logic with others.

Understanding Runtime Errors

Crystal Reports 10 provides the ability to get more information about variables within formulas when a runtime error occurs. In the past, when a runtime error (such as a Divide by Zero) occurred, Crystal would simply take you to the line of the formula giving the error. However, this was not altogether helpful, especially if the error was because the data being passed in from the database could have been at fault. So, in version 9 of Crystal Reports, there is a new feature that shows all variables and data field values used in all related formulas when an error occurs. You can think of this as a variable stack.

The runtime error stack only appears when a runtime error occurs (when real-time data forces an error). It appears where the workshop group tree normally would in the Formula Workshop.

The runtime error stack shows all variables and all database field data related to the formula in question. If custom functions are called within the formula, their variables will appear above the formula as well. The last function to be called will appear at the top.

TIP

The idea of a stack (reverse order) is useful in that the last function called most likely will be where the error is. But, of course, that might not always be the case.

This concept is best shown as an example. Assume that Xtreme Mountain Bike Company's management would like to take the Chap13_1.rpt and find out how much money is not accounted for by days when not shipped (Calculation = Order Amount / Days until shipped). To see how this works, follow these steps to simulate a formula error:

1. Use the Chap13_1.rpt again. If it's not already open, open it by choosing Ctrl+O.
    
2. Use the Formula Workshop. Select Report, Formula Workshop. Right-click on the Formula Field branch in the workshop tree and choose New. Name the formula Unaccounted Amount/Day and select Use Editor.
    
3. Add the required logic. In the Editor, enter the following: "{Orders.Order Amount} / {@Days until Shipped}". Click the Save and Close button in the top-left corner. Choose Yes when prompted to save. If the report is not already in Preview mode, press F5 to refresh the report. If you don't see any data, choose Report, Section Expert and make sure that the Details section isn't suppressed. If it is, toggle the option and click OK.
    
4. Drag the field onto the report. From the Field Explorer (View, Field Explorer), select the newly created formula and drag it onto the report to the right of the Days Until Shipped field. Notice that the Divide by Zero error comes up right away. Click OK.
    
5. View the Runtime Error Stack shown in Figure 13.4. In this case, the formula is quite straightforward. The problem is occurring because some of the orders are on time (zero days wait). Xtreme's management would like to show 0 if the orders are on time, so change the formula to the following: "if {@Days until Shipped} = 0 then 0 else {Orders.Order Amount} / {@Days until Shipped}". Click the Save button.
    

   Figure 13.4. Runtime Error Stack next to the newly updated formula.

    
6. Click F5 to refresh the report. See the values of the resulting formula as shown in Figure 13.5 and then save the report as Chap13_3.rpt.
    

   Figure 13.5. Resulting Report with the latest Xtreme requirements added.

    

Crystal Reports in the Real WorldCustom Functions

As described in Chapter 4, custom functions can be prepared in advance and can be stateless so they can be used later in a variety of ways. In the next example, a name formula is created in such a way that it builds and formats names in a consistent and reusable manner.

For more detailed information on how custom functions can be prepared in advance, p. 95

 

1. Open the Employee Profile report. From the Field Explorer, select Formula Fields and click New. Give the formula the name Title and click Use Editor. Enter the following text into the code window of the formula editor:
    
    

   If InStr(LowerCase({Employee.Notes}), " he ")>0 Then
   
    "Mr."
   
   Else If InStr(LowerCase({Employee.Notes}), " she ")>0 Then
   
    "Ms."
   
   Else
   
    "";
   
   

    
2. Click Save and Close. Next, create another new formaula named Suffix and in the code window only type two double-quotes (the string equivalent of NULL); this acts as a placeholder because the table doesn't have a suffix field. Finally, create a formula called Proper Name and add the following text into the code window:
    
    

   Local StringVar strFullName;
   
   strFullName := "";
   
   If {@Title} <> "" Then
   
    strFullName := {@Title} & " ";
   
   strFullName := strFullName & {Employee.Last Name} & ", " & {Employee.First Name};
   
   If {@Suffix} <> "" Then
   
    strFullName := strFullName & " " & {@Suffix};
   
   strFullName
   
   

    
3. Click Save and Close. From the Report menu, choose Formula Workshop. Click New and give it the name ProperName and click Use Extractor. In the list of formulas, select @Proper Name. Your screen should look like Figure 13.6. By default, the Argument Names are v1, v2, v3, and v4 but this won't help users of your formula so change the names to Title, LastName, FirstName, and Suffix.
    

   Figure 13.6. Custom function properties.

    
4. Click OK to close the window. Crystal converts the formula from the way it's currently written into a generic custom function for later use. The new custom function is shown in Figure 13.7.
    

   Figure 13.7. Custom function formula.

    
5. Click Save and Close. The custom function is now available for use.
    
6. Finally, create the formula that will be used in the report. Create a new formula called Custom Name. From the list of functions, double-click the ProperName function and pass in the following values:
    
    

   Title: @Title
   
   LastName: {Employee.Last Name}
   
   FirstName: {Employee.First Name}
   
   Suffix: ""
   
   

   
   Your completed formula should look like Figure 13.8.
    

   Figure 13.8. Passing values to a custom function.

    

   NOTE

   In the sample database there is no Title field, but a formula can be used to generate the Title that will be passed to the custom function. The sample database also doesn't contain a suffix field and because there's no way to determine if the employee name has a suffix, a null string will be passed to the custom function. The custom function can be used later with tables that have both Title and Suffix fields.

7. Add the Custom Name formula to the report and replace the @name formula in the Group Header 2 section. Change the font to Bold and white. Save the report as CustomName.rpt. Figure 13.9 shows the completed report.
    

   Figure 13.9. Report using a Name built using a custom function.