When creating reports in Crystal Reports for Blackbaud, you can conditionally display field contents. If you want to view specific field entries rather than every record in the data file, you must create a formula to specify what will be displayed and when it will be displayed. If..then..else.. formulas range from simple to complex.

Note: This document assumes you are comfortable inserting and removing fields from reports.

To use a formula in report:  

  1. Create a new formula
    •  Open the Field Explorer (Crystal XI: View > Field Explorer; Crystal 8.5: Insert > Field Object)
    • Right-click on Formula Fields
    • Select New
    • Enter a name for the formula
    • Click OK
  2. Use the Formula Editor to write the formula.

Formula Editor


Example 1

You want to display the text "Major Donor!!" if the gift amount is over $1,000 and the text "Sustaining Donor!!" if the gift amount is under $1,000.

In this case, you would write the following if.. then.. else.. formula: if Gift Amount is greater than $1000.00 then "Major Donor!!" else "Sustaining Donor!!"

  1. To begin this formula, type the word if in the Formula text box
  2. Double-click on the Gift Amount field from the Report Fields list
  3. The formula text now reads: if {Gf_Amount}
  4. Type the greater than symbol and the rest of the text: if {Gf_Amount} > 1000 then "Major Donor!!" else "Sustaining Donor!!"

    You must type quotes around the text so Crystal Reports can understand you are not referring to a field in the data file. When you include numbers, you do not need to specify the dollar symbol or the decimal unless you specify cents.

  5. When you finish writing the formula, click Check (X+2 button with a green checkmark underneath). Crystal Reports will check the formula syntax for errors. If the formula is correct, the message "No errors found" appears.

    Note: The Gift Amount field appears in brackets when you select it from the list. The brackets designate that this is a data field in the formula.

  6. Click Save and Close. The formula appears in the Formula Fields list. You can place it anywhere in the report. Remember that the group in which you place the formula can have an affect on the results.

When you click Check or Save and Close, Crystal Reports checks the formula's syntax, not the formula's logic. Always check the actual data you receive when you use a formula field so you can catch mistakes early and make changes to your formula if necessary.




 

Example 2

You want to display the gift amount only when the gift date is within a certain range so you can generate subtotals.

In this case, your formula's logic is as follows: if Gift Date is within the range 1/1/1999 to 6/30/1999 then Gift Amount else 0

The formula's actual syntax is a little different to allow Crystal Reports to understand what you need: if {Gf_Date} in Date(1999,01,01) to Date(1999,06,30) then {Gf_Amount} else 0

Note: Crystal Reports uses the word in to specify a date range. Also notice the specific format you must use for dates in formulas: Date(4 digit Year, 2 digit Month, 2 digit Day)






Example 3

You want to display the gift amount only when the gift type is Cash, Pay-Cash, or MG Pay-Cash.

In this case, your formula's logic is as follows: if Gift Type is one of these values: Cash, Pay-Cash, MG Pay-Cash then Gift Amount else 0

The formula's actual syntax is a little different to allow Crystal Reports to understand what you need: if {Gf_Type} in ["Cash", "Pay-Cash", "MG Pay-Cash"] then {Gf_Amount} else 0

Specify that something is within an array with the word in followed by a set of square brackets. Also notice that the words "Cash", "Pay-Cash", and "MG Pay-Cash" are exact contents of the Gift Type field. Because these are text fields, you must enclose them in quotations. You can either type the field words in the formula or right-click the Gift Type field and select Browse Field Data to select the entries.





Example 4

You want to display the text "Too Low" if the gift amount is less than $100.00.

Data types for the Then and Else portion of the formulas must be the same data type.

An example of incorrect syntax formula is as follows: if {Gf_Amount} > $100.00 then {Gf_Amount} else "Too Low!"

When you click Check, you would receive the error message "A currency amount is required here" because Crystal Reports will not accept this syntax.

The problem with the syntax in this example is that the gift amount and the text are not the same data type. The gift amount is a currency field and can be formatted with currency signs, decimal places, etc. The text "Too Low!" cannot be formatted in the same way because it is a string field. Since the formula cannot return both types of fields, the formula is invalid.

We can convert the currency field to a string in order to use the formula: if {Gf_Amount} > $100.00 then ToText(Gf_Amount) else "Too Low!"





Example 5

You want to display a person's middle name if it exists. If not, display only the first and last name.

In this case, your formula's logic is: if middle name is blank then First Name + Last Name else First Name + Middle Name + Last Name

The formula's actual syntax is: if isNull({Middle Name}) or {Middle Name} = "" then {First Name} + " " + {Last Name} else {First Name} + " " + {Middle Name} + " " + {Last Name}

Note: If the isNull() function is being used in the formula, it must appear at the beginning of the formula.





Example 6

You want to display the Salutation field but if this field is blank, then display the Primary Addressee.

In this case, your formula's logic is: if Salutation is blank then Primary Addressee else Salutation

The formula's actual syntax is: if isNull({SALUTATION.EN_US}) or {SALUTATION.EN_US}="" then Primary Addressee else Salutation

Note: if the isNull() function is being used in the formula, it must appear at the beginning of the formula.






If you have questions on Crystal Reports for Blackbaud and writing formulas you can post specific questions to the Crystal Reports Forum.