The word formula comes from the Latin word forma, meaning shape or mold. This is appropriate because formulas in Crystal Reports are about to mold, shape and change your world. There are an unlimited number of ways formulas can be used. I’ve seen some really cool and creative ways to use formulas and I’m still learning different ways to use them.
Let’s ease into learning about formulas with a simple need for our report. We want to create a column that shows the gift amount but only for cash gifts. Keep in mind that we still want to show all gifts on the report so the purpose here isn’t to apply any filters to the report. When I say “cash gifts” I’m referring to gifts with a Gift Type = Cash.
On the right side of your Crystal Reports screen you should see a panel called Field Explorer.
If the Field Explorer is not visible click on the View menu on the top toolbar, then select Field Explorer. There is also a Field Explorer button that looks like this:
To create a new formula field right-click on the Formula Fields heading and select “New…” This will open the Formula Name window. Enter a name for the formula. I am going to name our formula field “Cash Gift Amount.” Be sure to use descriptive names that make sense so you (and the next employees in your position) will know what the formula field is. After you click the OK button the Formula Workshop – Formula Editor window will appear.
There’s a lot going on here but don’t be afraid. For now we only need to click in the blank area on the bottom center/right of the window. This is called the Formula Editing area and is where we, um, edit formulas. Click anywhere in this area and a text cursor will appear, blinking and waiting for your command.
The command will be a formula that says if the Gift Type is “Cash” then display the Gift Amount, otherwise display zero. Let’s translate this into a formula in the Formula Editor.:
If you just want to type in the formula without all the steps then skip to the last bullet point in the list.
- Type If
- Above the Formula Editor are three windows, with the one on the left being the Report Fields area. Click on the plus sign to the left of the data file path heading (in the image above this shows as H:\SampleGiftExport…). This will expand to show the tables in the data file. Click the plus sign to the left of the Gf table, which will expand to show the fields in the table. It should now look something like this:
- Double-click on the Gf_Type field name. This will insert the field name in the Formula Editor. Make sure there is a space between the word If and the field name.
- Type =
- Right-click on the Gf_Type field name in the Report Fields area and select Browse Data. This will open a window with a list of all the data that is in the Gf_Type field.
- Double-click on Cash. This will enter “Cash” in the Formula Editor window. Your formula should now look like this: If {Gf.Gf_Type} = “Cash”
- Type then
- Double-click on the Gf_Amount field name in the Report Fields area. This will insert the field name in the Formula Editor.
- Type else 0
- Your formula should now look like this: If {Gf.Gf_Type} = “Cash” then {Gf.Gf_Amount} else 0
Click the Save and Close button (upper-left corner of the Formula Workshop window). Crystal Reports will check your formula for errors before closing and will display a warning message if there are any errors.
The new formula field will now appear in the Field Explorer under the Formula Fields header:
The Cash Gift Amount can now be added to the report just like a regular data field. Click and drag on the Cash Gift Amount field name and drop it to the right of the Gift Amount (Gf_Amount) field that is already on the report in the Details section. Your report should now look like this:
Go ahead and try to add the second column we need for pledges following the same steps above. We have some cleanup to do and I’ll cover that in the next article. Also let me know (in the comments section below) if there are specific formulas you would like me to cover and I’d be glad to.
