Creating a New Formula | Mastering Business Analysis with Crystal Reports 9 (Wordware Applications Library)

The creation of formulas is made easy through a part of the Formula Workshop known as the Formula Editor. The Formula Editor is where you actually create and modify the content of formulas. The Formula Workshop is simply a means of holding all your report's areas within one area.

To jump into the world of reporting formulas, try the following steps. Use the report that you've started within this chapter.

Open the Formula Workshop by selecting Report, Formula Workshop from the menu bar.
To access the Formula Editor, select the arrow next to the New button.

This button is located within the toolbar of the Formula Workshop, as shown in Figure 10-2.

Figure 10-2: Creating a new formula from the Formula Workshop

From the arrow next to the New button, you'll see a list of different components that you can create.
Select the Formula option from the drop-down menu.

A Formula Name dialog box displays.
Enter the formula name EmplName.

Your screen should look like Figure 10-3.

Figure 10-3: Naming a formula field
Click the Use Editor button.

The Formula Editor displays.

Note

The Formula Expert allows you to create formulas based on existing custom functions. This is an advanced feature, useful for enhancing or editing already existing formulas. For the purposes of this book, you won't need to utilize the Formula Expert.

At first glance, the Formula Editor may seem a little scary. However, don't worry (be happy). Its bark is worse than its bite! Figure 10-4 displays the Formula Editor.

click to expand
Figure 10-4: The Formula Editor

The Formula Editor allows you to add, edit, or delete the detail behind your formula fields. The Formula Editor consists of the following four windows:

Functions: Contains the prewritten procedures included within Crystal Reports. These procedures perform a variety of calculations.
Report Fields: Contains all the database fields available within your report. This window also contains any groups and other formulas already created within your report.
Operators: Contains the symbols that represent a specific action. Think of operators as the "action verbs" that you use in formulas. Operators describe the action that takes place between two or more values.
Formula text window: Contains the area where you write and edit the content of your formulas.

Note

The Workshop Tree displays to the left side of the Formula Editor.

Within the Formula Editor, you should see the different formulas that you create reside within the appropriate folder. The Formula Workshop is Crystal Reports' way of organizing your work.

In the Formula Editor, you create formulas by double-clicking any of the components within the Report Fields, Functions, or Operators windows. When you select a component, the required syntax is also inserted automatically.

Of course, if you're a glutton for punishment, you can also manually type every aspect of your formulas.

Creating a Concatenating String Formula

Now that you're somewhat familiar with where formula creation takes place, let's actually create some formulas.

At this point, you should have already created a new report and started the creation of a formula with the name EmplName. If you have not done these steps, flip back through this chapter to get yourself up to this point.

Once you've done these things, try writing your first formula. Notice that the Last Name and First Name information display in separate fields. This first formula combines these two fields into one and separates them with a comma, so the fields display in the format: last name, first name.

In the world of programming, this is known as concatenating fields. Concatenating means to link together or join. Thus, what this formula does is create a string field that links together the last name and first name fields from the Employee table.

Concatenating fields is useful. Often, you'll need to create such formula fields for addresses and your customers and employees.

Perform the following steps. Continue using the report example that you've been working on within this chapter.

In the middle window, expand the Report Fields node.

You'll see the four fields that currently exist within your report.
Double-click the Employee.Last Name field.

This field displays within the formula text window located in the lower half of the Formula Editor.
After the (Employee.Last Name) field, type the following: +","+.

This syntax states that you're adding a comma, followed by a blank space, between the Last Name and First Name fields.
After you've typed in that information, double-click the Employee.First Name field, located in the Report Fields window.

The results of your work should look like Figure 10-5 on the following page.

Figure 10-5: Creating a string formula

After typing in your code, press Alt+C. You can also press the Check button next to the Save button (the Check button contains the text "x+2" with a green check mark below). Your formula should display the message "No Errors Found." Press OK on this message.

Finally, press the Save button and close the formula. You can close your formula using the Close button at the top left of the screen (above the Formula Workshop folders). Or you can press the X button at the top right of the screen to exit. You're returned to the Report Design area.

Congratulations — you've just become a programmer!

To see your work in action, try the following. Continue using the report that you've created within this chapter.

Select the Design tab.
Delete the Last Name and First Name fields from the Details section.
From the menu bar, select Report, Group Expert.

The Group Expert dialog box displays.
Press the Remove (<) arrow to remove the Employee.Last Name field as your group.
You'll notice the EmplName formula you just created now displays at the bottom of your report fields. Select this field, and press the Add (>) arrow button.

Your report will now group data by your formula field.

Press the OK button on the Group Expert. You may want to move the Order Date and Order Amount information slightly to the left to improve the appearance of your report.

Once you're satisfied with the report's look, try previewing your work. Your report should look something like Figure 10-6.

click to expand
Figure 10-6: Grouping a report by a formula field

Of course, formula fields can be added to any section of your report (not just groups). Go to the Field Explorer window to add a formula field to another section (such as the Details section). Expand the Formula Fields node to display a list of all your formula fields. To add any formula field to your report, you can simply drag the formula field to the appropriate section.

Creating a Conditional Formula (Using Functions)

Up to this point, you've learned about creating formulas using arithmetic operators such as the plus sign (+) or the greater than or equal (>=) symbol.

There are two more operators that are just as useful, if not more so, than the arithmetic operators. These are the conditional operators. Conditional operators evaluate a statement's result as either true or false (known as the Boolean result). The two operators used with conditional statements are IF THEN and ELSE.

The IF THEN and ELSE operators work in the following manner:

If the IF clause is true, the formula performs the functionality defined within the THEN clause.
If the IF clause is false, the formula performs the functionality defined within the ELSE clause.

For example, say you wished to create a formula that alerts report users when an employee's sales are under $150,000 for the year. Your conditional expression would state something like this:

IF an Employee's Sale Total for the Year were less than $150,000,
THEN print the Order Total in red,
ELSE print the Order Total in black.

If the previous conditional expression existed within your report, the formula would first check to see if an employee's order total (for the year) equals less than $150,000. If this IF clause is true, then the order total displays in red. If the IF clause is false, the employee's order total (for the year) must be greater than $150,000. Therefore, the order total prints normally (in black).

This concept is much easier to understand in practice. Try the following example, using the report you've created within this chapter:

Select Report, Record Sort Expert from the menu bar.

The Record Sort Expert displays.
Add the Orders.Order Date field (from the Report Fields node) to the Sort Fields list box.

The Order Date field displays underneath the @EmplName formula field.
Press the OK button on the Record Sort Expert dialog box.

You're returned to your report.
Open the Formula Workshop.
Select the arrow next to the New button and select the Formula option.
Call this new formula Quarter. Then, press the Editor button.

The Formula Editor displays.

Type the following code within the Formula Edit window:

 IF Month({Orders.Order Date}) in [1, 2, 3] THEN "Quarter 1" ELSE IF Month({Orders.Order Date}) in [4, 5, 6] THEN "Quarter 2" ELSE If Month({Orders.Order Date}) in [7, 8, 9] THEN "Quarter 3" ELSE IF Month({Orders.Order Date}) in [10, 11, 12] THEN "Quarter 4"

The "Month" word is a function, available from the Functions window (expand the Functions node and then the Date and Time node). The Month extracts the month component of a date and converts it to a number.

You'll see the Month (x) function within this node, as shown in Figure 10-7.

click to expand
Figure 10-7: Expanding the Functions window

Once you've written this code, check the formula for correct syntax (by using the Check button). Then save the formula, and close the Formula Editor. You're returned to your report.

A Word about Functions

Most programming languages (Crystal included) come with a built-in set of procedures, known as functions. When a developer uses a function, the developer is using previously coded tasks rather than having to devise the code from scratch. Thus, functions are essentially prewritten code that you can use within your formulas.

Check out Appendix C for a summary of all of Crystal Reports' built-in functions.

The formula you've just written says the following:

IF the month portion of the Order Date record is equal to 1, 2, or 3, THEN display the text "Quarter 1" ELSE
IF the month portion of the Order Date record is equal to 4, 5, or 6, THEN display the text "Quarter 2" ELSE
IF the month portion of the Order Date record is equal to 7, 8, or 9 THEN display the text "Quarter 3" ELSE
IF the month portion of the Order Date record is equal to 10, 11, or 12 THEN display the text "Quarter 4"

If you're confused about how this formula works, maybe the best thing to do is see it in action. Check the syntax of your formula. If no errors are found, save your work and close the Formula Workshop. You're returned to your report.

In the Field Explorer pane, expand the Formula node. You'll see your newly created Quarter formula. Move this formula field to the .5" mark within the Details section.

Once you've done this, go to the menu bar and select Report, Group Expert. The Group Expert displays.

In the Group Expert, create a new group. This new group should reside underneath the EmplName group that you created earlier in this chapter. Once you've added this group, press the OK button on the Group Expert. You're asked if you wish to use saved or refreshed data. Press the Refresh Data button. Your report should look something like Figure 10-8.

click to expand
Figure 10-8: Adding a conditional formula to your report

Notice that your report now groups your data by quarter. Any months within January, February, or March are listed as Quarter 1, any months within April, May, or June are listed as Quarter 2, and so on.

Using the ToText and Year Functions

Unfortunately, most reports that display quarter information also display the year (for example, "Quarter 1, 2000" rather than just "Quarter 1"). So, how would one go about adding such information?

First, you need to extract the year from the Order Date data. To do that, you can use the Year function. The Year function extracts the year from a date and returns the value as a number.

From the menu bar, access Report, Formula Workshop. Then, expand the Formula node, and double-click the Quarter formula you created earlier. This formula displays within the Formula Editor. Change your formula in the Formula Edit window to look like the following:

 IF the month portion of the Order Date record is equal to 1, 2, or 3, THEN display the text "Quarter 1," + Year({Orders.Order Date}) ELSE IF the month portion of the Order Date record is equal to 4, 5, or 6, THEN display the text "Quarter 2," + Year({Orders.Order Date}) ELSE IF the month portion of the Order Date record is equal to 7, 8, or 9, THEN display the text "Quarter 3," + Year({Orders.Order Date}) ELSE IF the month portion of the Order Date record is equal to 10, 11, or 12, THEN display the text "Quarter 4," + Year({Orders.Order Date})

Once you've written this code, try checking the syntax.

Uh oh — there's an error (which is correct for this example; you'll see why in a moment). You should get the following error, as shown in Figure 10-9.

Figure 10-9: A formula syntax error

Whenever you receive an error, Crystal Reports describes the reason for the error within a message box and then highlights the problem area within your code.

The reason you received this error is that a formula cannot return a result made up of different data types. In other words, a formula result can't combine a text string (for example, "Quarter 1") with number data (for example, "2000"). So what can you do to correct this?

What you'll need to do is translate the year of the Order Date into a text string (to match the "Quarter 1" text string). A useful function that performs this is the ToText function. The ToText formula simply converts any data type into a string.

Caution

All results of your formula must end up as the same data type; otherwise you will receive errors.

If you haven't done so already, press the OK button in the error message box. Let's revisit your Quarter formula again. Type the following changes (marked in bold) into the formula. You can look at how this formula works in a moment — after writing it. Figure 10-10 displays how your screen should look.

click to expand
Figure 10-10: Adding the ToText function to a formula

 IF the month portion of the Order Date record is equal to 1, 2, or 3, THEN display the text "Quarter 1," + ToText(Year({Orders.Order Date}), 0, "") ELSE IF the month portion of the Order Date record is equal to 4, 5, or 6, THEN display the text "Quarter 2," + ToText(Year({Orders.Order Date}), 0, "") ELSE IF the month portion of the Order Date record is equal to 7, 8, or 9, THEN display the text "Quarter 3," + ToText Year({Orders.Order Date}), 0, "") ELSE IF the month portion of the Order Date record is equal to 10, 11, or 12, THEN display the text "Quarter 4," + ToText(Year({Orders.Order Date}), 0, "")

Check the syntax of your formula. If you find no errors, save the formula and close the Formula Workshop. Figure 10-11 displays the results of this new formula. Notice the quarter information now displays the year.

click to expand
Figure 10-11: The results of your new Quarter formula

Before summarizing how this formula works, let's look briefly at the ToText function that you just used within this formula:

ToText(Year({Orders.Order Date}), 0, "")

While this looks confusing, it truly isn't. It's all part of the ToText function's syntax. Check out Appendix C for a summary of the syntax of all the Crystal functions.

The ToText function's syntax is as follows: ToText (x, y, z, w)

x is the value you wish to convert to a text string. In the example in this chapter, the x value is the Year function, which contains the year of the Order Data field.
y is a whole number that indicates the number of decimal places to use with the x value. In the example in this chapter, the y value is 0 (you don't want any decimal places with the year).
z is a single-character text string that indicates the character to use to separate the thousand's place in the x value. In the example in this chapter, the z value is "". This represents no space. Thus, no character shall separate the thousand's place in the x value. Since the x value is a year, you want your year to read 2000, not 2,000.
w is a single-character text string that indicates the character to use as a decimal separator in the x value. Since we designated 0 decimal places in the y value, we don't need to include this value.

The default is the character specified in your International or Regional settings control panel.

With functions, you can use as many or as few of these values as you need.

There's just one final observation to note with this last formula. Notice that you changed the "Quarter x" text to read "Quarter x,". The reason for this was to add a comma and space, so your ToText result displays correctly with the rest of your text string.

Save your work as EmplOrders.rpt and then take a breather — you deserve it. You've absorbed a lot in this chapter.