New in Crystal Reports Version 9

Although the use of formulas in Crystal Reports is one of the most basic of design functions, ways to improve formula usage always exist. This latest version focuses on productivity and usability improvements.

Much of the usability features around formulas have already been addressed in this book, mostly in Hour 10, "Understanding and Implementing Formulas." Therefore, the focus here will be on the functional and productivity enhancements made around formulas.

Memos 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 in version 9.

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. That is not a problem! 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. We will now 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 will have it in c:\program files\crystal decisions\crystal reports 9\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. Click on the Set Default Values button. In the Set Default Values dialog, add "<none>", "she", and "he" to the default values. The final result should look like Figure 17.1. Click the OK button. The Create Parameter dialog should look like Figure 17.2. Click OK.

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

   

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

   

3. 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} = "<none>" THEN TRUE ELSE ({?Search-a-Word}) IN LowerCase({Employee.Notes}) and press the Save and Close button.

4. 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 will appear on the report as shown in Figure 17.3. Save the report as Chap17_1.rpt.

   Figure 17.3. The Employee Profile showing female employees only.

   

Notice that we only put the LowerCase() function call around the {Employee.Notes} field and not the parameter. This is because we put the values into the parameter as lowercase by default. However, because we 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 will allow Crystal to compare apples to apples when evaluating these exact values. Alternatively, both could have been set to UpperCase() as well.

Not all databases support the ability 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 datasource but only the 6 that were female were shown on the report. This is because the datasource 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 would have been originally expected.

A keyword search is just one example of how to use a memo field in a formula. The 255 character limitation for formulas being 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.

Additional Financial Functions

In previous versions of Crystal Reports, the financial functions capability of the formula language was limited to 13 functions. However, in version 9 of Crystal Reports, more than 50 financial functions are now available. With overloads for parameters, these functions count up to about 200 variations.

These functions were implemented in order to give as much functionality as possible to a very 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 MS 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 9 Help file. In the Index, look up "Financial Functions" for a complete list of what's available.

Custom Functions

Custom Functions are completely new to Crystal Reports 9. Although they have been introduced in Hour 10 already, this section focuses on some more detailed information on what they are and how they could be used in Report Development.

What Are Custom Functions?

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's 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 9:

 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 a 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 is not necessarily needed 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 (after all the functions return) to be passed back out to the formula making the call.

Creating Custom Functions from Existing Formulas

For many report developers, they might already have fully functional formulas that would make great custom functions. Making the necessary changes to the function format feels like a large task. The good news is that Crystal Reports 9 contains a feature that actually does this for the report developer. It's called the Formula Extractor. To get a better understanding of what the Formula Extractor does, here is a working example:

1. Open the Chap5.RPT. Press Ctrl+O to get the Open dialog and browse until Chap5.RPT is found. Select it and click OK.

2. Extract the formula. Select Report, Formula Workshop and right-click on the Report Custom Functions item in the Workshop group tree. Select New. Call the Custom Function DaysUntilShipped and then select Use Extractor.

   

   Because a Custom Function is similar to a reserved word, there can be no spaces in the name. If a space is required, consider using the underscore instead.

   
   

3. Choose the formula created earlier, also called DaysUntilShipped. Because the name of the Custom Function has already been entered, it is automatically placed in the Name box.

4. Add the necessary information. Add the following text to the Summary box: This function takes two dates and determines the number of business days between them. Also, rename v1 to Start and v2 to End. Add Start Date as the description for the first variable (Start) and End Date as the description for the second variable (End). Finally, make sure that Modify Formula to Use New Custom Function is selected. The end result should look like Figure 17.4.

   Figure 17.4. Extract Custom Function from Formula dialog.

   

   

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

   
   

5. Save and close the newly formed custom function. By clicking the OK button, Crystal Reports returns to the Formula Workshop with the Editor view of the newly created function. In the workshop tree, navigate to Formula Fields, Days Until Shipped to see the results. The formula now uses the new custom function DaysUntilShipped as indicated in Figure 17.5. Close the Formula Workshop by clicking on the X in the top-right corner of the dialog.

   Figure 17.5. Formula converted to use custom function.

   

6. Save the report as Chap17_2.rpt by choosing File, Save As.

Creating custom functions from existing reports can be handled quickly and easily using the Formula Extractor. If you require creating custom functions from scratch, see more information in 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

New in Crystal Reports 9 is 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 the report developer 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 for mula 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.

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. Let's assume that Xtreme Mountain Bike Company's management would like to take the Chap17_1.rpt and find out how much money is not accounted for by days when not shipped (Calculation = Order Amount / Days until shipped).

1. Use the Chap17_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. As shown in Figure 17.6, see the runtime error stack and how it works. 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 17.6. 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 17.7 and then save the report as Chap17_3.rpt.

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