When creating a report using Crystal Reports for Blackbaud, you may need to use variables for Crystal Reports to calculate your data correctly. According to the Crystal Reports Help Menu, a variable is: "Variables are a special kind of value that you can use in a formula. Unlike a constant value, which is fixed and unchanging, a variable holds a value that may change from time to time. A variable is like a container that can hold one value at a time. You assign a value to a variable, and the variable maintains that value until you later assign a new value. The variable then maintains the new value until you later assign a newer value, etc. When you use a variable in a formula, the formula looks to the variable and uses its current value in calculating the formula result. If the variable value changes, the formula looks to the new value and uses that to calculate the new result."

For example, if your report contains the field gift amount, the same gift amount may duplicate on the report several times. Suppressing duplicates may not always work because Crystal Reports may not always decipher it as a duplicate. When you subtotal or grand total your report, the gift amount will be calculated as many times as it shows up on the report, when it should be calculated in the totals only once. Variables usually require three formulas with each placed in the appropriate report sections. The first is a RESET formula, which tells Crystal Reports when to stop counting and start over again. The second is a CALC formula, which tells Crystal Reports how to calculate the values. The final formula is a DISPLAY formula, which tells Crystal Reports to display its calculation results. To reset the variable to 0 each time the row changes, we must first create a RESET and CALC formula for the Row that we are calculating.

@ Row Reset whileprintingrecords; numbervar row; row := 0

Because gift amount is the field that is being counted numerous times, we must first insert a Group Section on _Gift_ID. Place the @RowReset formula in the _Gift_ID header section of the report. This resets the variable each time the gift ID changes, which tells the report to only calculate the same gift once. This field should be hidden.

@ Row Calc whileprintingrecords; numbervar row; row := row +1

The @ Row Calc formula should go in the Details report section. This field should be hidden.

To obtain an accurate Grand Total, we must create three variable formulas: GT RESET, GT CALC, and GT DISPLAY.

@GT Reset whileprintingrecords; currencyvar GT; GT := 0

The @ GT Reset formula should go in the TITLE report section of the report. This field should be hidden.

@ GT Calc whileprintingrecords; currencyvar GT; numbervar row; {@row calc}; if row =1 then GT := GT + {Gift.Gift Amount}

The @ GT Calc formula should go in the Details report section. This field should be hidden.

@ GT Display whileprintingrecords; currencyvar GT; GT

The @ GT Display formula should go in the Grand Total report section. This field should be shown.

If you need to subtotal your report (for example, on constituent name), first insert Group Section on Constituent Name. Three variable formulas are needed: ST RESET, ST CALC, and ST DISPLAY

@ ST Reset whileprintingrecords; currencyvar ST; ST := 0

The @ ST Reset formula should go in the group header section of the report. This field should be hidden.

@ ST Calc whileprintingrecords; currencyvar ST; numbervar row; {@row calc}; if row =1 then ST := ST + {Gift.Gift Amount}

The @ ST Calc formula should go in the Details report section. This field should be hidden.

@ ST Display whileprintingrecords; currencyvar ST; ST

The @ ST Display formula should go in the Group Footer report section. This field should be shown.

Below are examples of a report, before and after using variables. This will help you better understand why variables are needed.

before using variables

In the above report, you can see that Anthony Bakker gave a $10,000 pledge; however it is listed five times (once for each installment). You know that the same pledge is listed five times because the Gift ID is the same (1) for each. Anthony Bakker also gave a $20,000 pledge and it is listed four times (once for each installment). You know that the same pledge is listed four times because the Gift ID is the same (323) for each. The same holds true for Benjamin Grant. As you can see, the subtotal for each constituent is inflated because it is counting the same gift each time it appears. The report should count the same gift only once. To do this, we must use the variables described above.

after using variables

 

Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Our Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.

We also offer customized Crystal Report templates. To learn more about our Custom Report Solutions, contact your account manager.