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 the purposes here, 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 XI sample report called Employee Profile. Most installations have it in the following folder: C:\Program Files\Business Objects\Crystal Reports 11\Samples\en\Reports\General 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.	Add "<none>", "she", and "he" to the list of default values. The final result should look like Figure 13.1. Figure 13.1. The default values and prompt settings for a Search-A-Word prompt.
4.	Connect the prompt 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} = "<none>" THEN TRUE ELSE ({?Search-a-Word}) IN LowerCase({Employee.Notes}) and click the Save and Close button.
5.	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.2. Save the report as Chap13_1.rpt. Figure 13.2. 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 XI 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 XI. Although they have been introduced in Chapter 4, 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 XI 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, since version 9 of Crystal Reports, there is a 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 World Sales Report 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 World Sales Report (open it from the same sample location referred to above for the HR report).
2.	Create a new formula called Days Until Shipped and use the following calculation: {Orders.Order Amount} / {@Days until Shipped}.
3.	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.
4.	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.
5.	Drag the field onto the report. From the Field Explorer (View, Field Explorer), select the newly created formula and drag it onto the detail line of the report. Notice that the Divide by Zero error comes up right away. Click OK.
6.	View the Runtime Error Stack shown in Figure 13.3. 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.3. Runtime Error Stack next to the newly updated formula.
7.	Click F5 to refresh the report. See the values of the resulting formula as shown in Figure 13.4 and then save the report as Chap13_3.rpt. In the sample report created here, the new formula was colored red so it can be easily found. You will need to drill to the detail level to see this field. Figure 13.4. Resulting Report with the latest Xtreme requirements added.