Variables in Formulas and Evaluation Times

Variables in Formulas and Evaluation Times

As a general rule, formulas contain their value only for the duration of one database record. If you put a formula in the details section, it will evaluate every time a new record is processed and put its result in the details section. If you put a formula in a group footer, it will be evaluated when each group footer prints. In every case, the formula will not remember anything from the previous record or previous group footer. Once the next record or footer comes along, the formula evaluates completely from scratch.

Sometimes, though, you may need a formula to remember material from record to record or from group to group. You may want to accumulate some value as the report progresses so that you can print a total in a group footer or report footer. For example, you may want to check the value of a subtotal in a group footer. If it exceeds a certain threshold, you may want to increment a counter so that you can show how many groups exceeded the threshold at the end of the report.

To accomplish this, you need to somehow store information from record to record or from group to group. This can be accomplished by using variables. A variable is simply a placeholder that Crystal Reports sets aside in the computer s memory. As the report progresses from record to record or from group to group, your formula can refer back to the variable or change its contents. You can then use the variable in other formulas or display its accumulated contents in a group or report footer.

Note  

Crystal syntax and Basic syntax use different statements to maintain variables. Just like in Microsoft Visual Basic, Crystal s Basic syntax requires use of the Dim statement to declare a variable before use. And as when working in Visual Basic, you can either assign a data type to a variable when you Dim it, or simply assign a value to it after you have used Dim without a data type (and the variable will automatically take on the data type of the value you assign it). Because of this similarity to Visual Basic, Basic syntax variables won t be discussed here, because they are well documented in Visual Basic texts . The rest of the discussion on variables applies to Crystal syntax.

Declaring a Variable

The first step in any formula that uses a variable is to declare the variable. This sets aside a specific amount of memory for the variable, based on its data type. You ll find variable declarations listed in the Operator Tree box of the Formula Editor under Variable Declarations.

Notice that a different variable declaration statement exists for each Crystal Reports data type. You must consider in advance what kind of data your variable is going to hold, and declare the correct type of variable accordingly . If, for example, you want to keep track of a customer name from record to record, and the customer name field in the database is a string data type, you need to declare a string variable to hold the information.

You must also give each variable a name. You can give it any descriptive name you wish, provided it doesn t start with a number, contain spaces, or conflict with another Crystal Reports formula language reserved word. You can t, for example, use variable names such as Date, ToText, or UpperCase ”these are reserved by the formula language for its own built-in functions (you ll know if your variable names are reserved words by looking at their color in the Formula Editor ”Crystal Reports turns all reserved words blue).

To declare a variable, type the variable declaration followed by the variable name, such as this example:

 NumberVar BonusAmount; 

This declares a number variable called BonusAmount that can later be assigned a numeric value. The semicolon at the end of the statement separates this statement from the next one in the formula (presumably a statement to assign or test the contents of the variable).

If you wish to use more than one variable in the formula, you may declare them together, again separated by semicolons. For example:

 NumberVar BonusAmount; 
 StringVar BonusCustName; 
 DateVar DateBonusReached; 

Tip  

You may be used to assigning variables in other programming languages. Remember that Crystal Reports probably treats variables differently. You must declare a variable in each formula where you want to refer to the variable. However, even if you declare a variable and assign it a value in one formula, and then declare it again in a formula that appears later in the report, it will retain the value from the first formula. Unlike in many other languages, declaring a variable more than once in Crystal Reports does not reset its value to zero or empty (with the exception of local variables, as described in the following section). These considerations apply to both syntaxes, Crystal and Basic. Even if you re used to using the Dim statement only once in Visual Basic, you must use it with Basic syntax in every formula where you want to refer to a variable. If the variable has been declared with a Dim statement in another formula, declaring it again will not reset its value.

Variable Scope

The whole idea and benefit of variables is that they retain their values as the report progresses from record to record or from group to group. So, for variables to be of real benefit, they need to keep their values throughout the report process. And because you may have several formulas that you want to refer to the same variable, you need to be able to refer to a variable in one formula that was already declared and assigned a value in another.

Exactly how long and where a variable keeps its value is determined by the variable s scope. If a variable has a narrow scope, it will retain its value only in the formula where it is initially declared ”any other formula that refers to a variable with the same name will be referring to a brand-new variable. If a variable has a wide scope, its value will be retained for use not only in other formulas, but also in subreports within the main report. (Subreports are covered in Chapter 13.) The following are three additional words you can place in front of your variable declarations (or use in place of the Dim statement in Basic syntax) to determine the variable s scope.

Local	The variable remains in scope only for the formula in which it is defined. If you declare a variable with the same name in another formula, it won t use the value from the first formula.
Global	The variable remains in scope for the duration of the entire main report. You can declare a global variable in one formula, and another formula will be able to use the contents placed in the variable by the first formula. Global variables, however, are not visible in subreports.
Shared	The variable not only remains in scope for the duration of the entire main report but can also be referred to in formulas in subreports. You can use shared variables to pass data around the main report, back and forth between the main report and subreports, and from subreport to subreport.

Add these keywords in front of variable declarations to determine their scope, as follows :

 Local NumberVar BonusAmount; //will only be visible in this formula 
 Global StringVar BonusCustName; //available to the whole main report 
 Shared DateVar DateBonusReached; //available to main and subreports 

Tip  

If you leave off the variable scope keyword in Crystal syntax, the default scope for a variable will be global ”it will be available to other formulas in the main report, but not to subreports. If you use the Dim statement in Basic syntax, the default scope for the variable will be local ”it will be available for use only in the rest of the formula where it s declared. If you don t want to use the default scope, make sure you always add the proper scope keyword. And, make sure you add the keyword to the declaration in every formula that will be using the variable.

Assigning a Value to a Variable

After you declare a variable, it won t do you much good if you don t assign a value to it. You may want to use it as an accumulator , to add one to it each time some condition is met for the database record. You may want to assign a string value to it, concatenating additional string values onto the variable as records progress. You then might display the value of the accumulated variable in the group footer, and assign the variable an empty string in the group header to start the whole process over again for the next group.

Tip  

If you declare a variable but don t assign a value to it, it takes on a default value based on its data type. Numeric and Currency variables default to 0, string variables default to an empty string, Boolean variables default to false, and Date variables default to a 0/0/00 date. Date/Time and Time variables have no default value.

Crystal syntax provides two instances in which you can assign a variable a value: at the same time the variable is declared, or on a separate line later in the formula. In either event, you must use the assignment operator, consisting of a colon followed by an equal sign, to assign a value to a variable. This is important ”it s easy to get confused and just use the equal sign by itself. The equal sign works only for comparison ”you must place a colon in front of the equal sign to make assignment work properly unless you are using Basic syntax, in which case the equal sign by itself is used for both assignment and comparison. Here s a Crystal syntax example of assigning a variable a value on a separate line:

 WhilePrintingRecords; 
 NumberVar CustomerCount; 
 CustomerCount := CustomerCount + 1 

Here, the CustomerCount variable is declared on the first line ( terminated with a semicolon) and assigned on the second line. In this particular formula, the CustomerCount variable will keep its value from record to record, so it will be incremented by one every time the formula executes.

If you want to reset the value of the CustomerCount variable in a group header, you need to reset it to 0. Here s a Crystal syntax example of how to declare and assign a variable at the same time:

 NumberVar CustomerCount := 0; 

Here, the variable is declared, followed by the assignment operator and the value to assign the variable. In this example, placing this formula in the group header will reset the CustomerCount variable at the beginning of each group.

Tip  

Notice that a semicolon doesn t have to appear in the last line of a formula, because it is used to separate one statement from another. If your formula only declares and assigns a variable, you don t need the semicolon at the end of the declaration/assignment statement.

You don t have to assign a value to a variable every time the formula executes, nor do you need to assign the same value every time. Creative use of logic constructs, such as If-Then-Else or Select Case, along with variable assignment, provides report flexibility that rivals that of many programming languages. Look at the following formula, which declares and conditionally assigns several variables:

 CurrencyVar BonusAmount; 
 StringVar HighestCustName; 
 DateTimeVar DateBonusReached; 
 
 If {Orders.Order Amount} > BonusAmount Then 
 (HighestCustName := {Customer.Customer Name}; 
 DateBonusReached := {Orders.Order Date}; 
 BonusAmount := {Orders.Order Amount}) 

Look at this formula closely. Assuming it s placed in the details section, it keeps track of the highest order amount as the records progress. When an order exceeds the previous high amount, the customer who placed the order and the date the order was placed are added to the variables. Then, the new high order amount is assigned to the bonus amount. The following are some important points to note about the formula:

• There are multiple variable assignments separated by semicolons inside the parentheses. They will all execute, but only the last statement will determine how the formula appears on the report. In this example, the last statement uses a currency data type, so the formula will appear on the report as currency.

• If you are keeping track of the bonus amounts, dates, and customer names for a certain group, such as a region or country, make sure to reset the variables in the group header. If you fail to reset the variables, and the next group doesn t have an order as high as the top value in the previous group, the previous group s values will appear for the following group as well.

• If you want to keep track of quotas or similar values for both group and report levels (for example, you want to see the bonus customer for each region and for the entire report), you ll need to assign and maintain two sets of variables: one for the group level that is reset in the group header and one for the report level that s not reset.

Displaying a Variable s Contents

In the preceding example, you saw how to accumulate values in variables in the details section, and how to reset them by assigning them a value of 0 in the group header (or in another area of the report). You also need to have a way to show exactly what s contained in a variable on the report, or to use the variable s value in a formula some other way.

To show the contents of a variable, you simply need to declare it. If the formula contains no other statements, declaring the variable will also return it as the formula value. For example, you might place the following formula in the group footer to show the customer who reached the bonus in the region group:

 StringVar HighestCustName 

You neither need to place any other statements in the formula to show the value of the variable nor even need the semicolon at the end of the declaration line ”it s the last line in the formula.

You may have situations in which you want to show the contents of a variable but are using other statements to assign the variable in the formula. In that case, just declaring the variable won t display it, because the declaration statement won t be the last line in the formula. In this situation, just add the name of the variable as the last line of the formula. This will then display the contents of the variable when the formula executes. Here s an example:

 CurrencyVar BonusAmount; 
 StringVar HighestCustName; 
 DateTimeVar DateBonusReached; 
 
 If {Orders.Order Amount} > BonusAmount Then 
 (HighestCustName := {Customer.Customer Name}; 
 DateBonusReached := {Orders.Order Date}; 
 BonusAmount := {Orders.Order Amount}); 
 
 HighestCustName 

This formula performs the test and variable assignments as before, but the last line of the formula simply shows the HighestCustName variable, a string variable. So, this formula shows up with small x s in the Design tab (if Show Field Names is turned off in File Options), and the contents of the HighestCustName variable will be shown whenever the formula executes.

You can go even one step further by testing and assigning variables and then using them later in other calculations or concatenations. Here s another permutation of this formula:

 CurrencyVar BonusAmount; 
 StringVar HighestCustName; 
 DateTimeVar DateBonusReached; 
 
 If {Orders.Order Amount} > BonusAmount Then 
 (HighestCustName := {Customer.Customer Name}; 
 DateBonusReached := {Orders.Order Date}; 
 BonusAmount := {Orders.Order Amount}); 
 
 "As of this order, the amount to beat is " & ToText(BonusAmount) + 
 " set by " & HighestCustName & " on " & 
 ToText(DateBonusReached,"M/d/yy") 

This formula not only declares variables, it also conditionally assigns them and then concatenates and displays them, converting them to text as necessary.

Evaluation Times and Report Passes

As you may have gathered by this time, formulas that contain variables are often affected by where they are placed physically on the report. If you want to check values and assign variables during record-by-record processing, you must put the formula in the details section. If you want to show the accumulated totals for each group, you place a formula in the group footer to show the total variables. To reset the variables for the next group, you need to place the formula that resets them in the group header.

However, just placing the formulas in these sections doesn t necessarily guarantee that they will actually evaluate in that section or during the logical formatting process of the report (during which a group header prints, then the detail sections for that group print, then the group footer prints, and so on). Consider the following example. Figure 5-4 contains a report that calculates a running total using a variable. The variable accumulates the order amounts in each detail section as the report progresses.

Figure 5-4: Running total using a variable

As you can see, the report is a simple detail report ”there are no groups. The running total is accumulating the orders as the report progresses. The formula contains the following variable assignment:

 CurrencyVar MonthlyTotal := MonthlyTotal + {Orders.Order Amount} 

In Figure 5-5, the report is grouped by Order Date, using for each month grouping. In this situation, the desire is to reset the running total for each month, as the viewer evaluates each month on its own. Accordingly, the running total variable MonthlyTotal must be reset in each group header with the following formula:

 CurrencyVar MonthlyTotal := 0 

Figure 5-5: Formula in group header to reset running total doesn t work

However, even when this formula is placed in the group header of the report, the desired result is not achieved.

Notice that not only did the running total not get reset to zero from the group header, but that it s showing a lower value than it was in the previous group. Why does adding a group result in the oddities with the running total? This happens because the formula is accumulating the running total at a different time from when it s actually displaying it on the report, and because the formula to reset the running total is evaluating at yet another time during report processing.

The formula to accumulate the running total is calculating while records are being read from the database, not when records have been grouped and are actually being printed or formatted. Also, the formula that resets the running total is actually being processed only once, at the very beginning of report processing, not when each group header prints. These formulas are said to be calculating in different report passes than the pass that actually formats the report. So, the running total has already been calculated for every record before Crystal Reports sorts the records to be placed in groups. Besides, the running total is actually being reset to zero only once before anything else happens on the report.

Crystal Reports generally breaks down its report processing into the following three passes, during which certain types of formulas automatically evaluate.

Before Reading Records	Occurs before any records are read from the database. If formulas don t include any references to database fields or summary functions, they calculate in this pass. These formulas are sometimes referred to as flat formulas.
While Reading Records	Occurs as records are being read from the database, but before any record selection, sorting, or grouping is performed. Formulas that include references to database fields, but don t contain any subtotal or summary functions, are calculated in this pass. These formulas are often called first pass formulas.
While Printing Records	Occurs after records have been read and are being formatted for display or printing. Sorting and grouping occurs during this pass. Formulas that include sum, average, or other summary functions are included in this pass. These formulas are often called second pass formulas.

In most cases, you can trust Crystal Reports to accurately determine in which pass it needs to evaluate a formula. The glaring exception, however, is when a formula uses variables. If a formula simply declares a variable, or declares the variable and assigns it a literal or constant value, Crystal Reports evaluates that formula in the Before Reading Records pass, because it makes no reference to database fields or summary functions. If you assign a variable a database value, Crystal Reports evaluates that formula in the While Reading Records pass (the formula will become a first pass formula). Only if you have some type of summary or subtotal function in the formula will Crystal Reports automatically evaluate the formula in the While Printing Records pass (the formula then becomes a second pass formula).

This default behavior can cause very strange results, as the previous running total example illustrates. The formula to accumulate the running total makes reference to the Order Amount database field and therefore evaluates in the first pass (WhileReadingRecords). This accumulates the running total just fine before the report was grouped. However, when the report was grouped by the Order Date, records appeared on the report in a different order than they were read from the database, resulting in the running totals no longer appearing in a logical order. And to complicate matters even further, the formula that resets the running total variable makes no references to database fields at all, so it becomes a flat formula (BeforeReadingRecords) and processes only once at the very beginning of report processing, instead of at the beginning of every group.

When you use variables in a formula, you may need to force the formula to evaluate in a different pass than it would by default. You do this by changing the formula s evaluation time. To do this, add an evaluation-time statement as the first statement in the formula. Look in the Formula Editor Function Tree box and you ll notice an Evaluation Time section. Open that section to see several evaluation-time statements that should now be mostly self-explanatory.

To force the formula that accumulates the running total to the second pass, where it will calculate the running total correctly after the records have been grouped, add the WhilePrintingRecords evaluation-time statement to the formula, as follows:

 WhilePrintingRecords; 
 CurrencyVar MonthlyTotal := MonthlyTotal + {Orders.Order Amount} 

Caution  

Don t get confused if you can t insert a subtotal, summary, or grand total on a second pass formula. When you click this type of formula in the details section, no subtotal, summary, or grand total options will be available on the pull-down or pop-up menus , because subtotals, summaries, and grand totals are calculated in the WhilePrintingRecords pass. If the formula is already evaluating in that pass, you can t create a summary or grand total on it.

Now, to ensure that the formula that resets the running total actually happens when the groups are being formatted, instead of one time only at the beginning of the report, force it to the WhilePrintingRecords pass as well.

 WhilePrintingRecords; 
 CurrencyVar MonthlyTotal := 0 

The one evaluation-time function that may not be self-explanatory is EvaluateAfter, which takes one argument: the name of another formula. This forces one formula to evaluate after another formula when they evaluate in the same pass and are in the same section of the report. Because Crystal Reports automatically evaluates formulas that contain other formulas in the proper order, you ll use this function very rarely. However, it may be necessary to use it with formulas that contain variables.

When Crystal Reports evaluates two formulas that contain the same variable in the same section of the report, the order in which it will evaluate them is not predictable. One example is if you place two formulas in a group footer. The first formula shows the values of the variables (assuming that those values have been set in other formulas in the details section):

 WhilePrintingRecords; 
 
 CurrencyVar BonusAmount; 
 StringVar HighestCustName; 
 DateTimeVar DateBonusReached; 

 "The highest order of " + ToText(BonusAmount) + 
 " was placed by " + HighestCustName + " on " + 
 ToText(DateBonusReached,"M/d/yy") 

The second resets the variables to zero or an empty string to prepare for the next group:

 WhilePrintingRecords; 
 
 CurrencyVar BonusAmount := 0; 
 StringVar HighestCustName := ""; 
 DateTimeVar DateBonusReached := DateTime(0,0,0); 

Because there s a chance that the formula that resets the variables will evaluate before the formula that shows them, you have two choices. First, and probably most logical, is simply to move the formula that resets the variables to the group header. That way, the variables will be reset when a new group begins, after they have been displayed in the previous group footer. Or if there is some logical reason why both formulas must exist in the group footer, you can use EvaluateAfter in the formula that resets the variables, as follows:

 EvaluateAfter ({@Bonus Show}); 
 
 CurrencyVar BonusAmount := 0; 
 StringVar HighestCustName := ""; 
 DateTimeVar DateBonusReached := DateTime(0,0,0); 

By placing EvaluateAfter as the first statement in the formula, you force the reset formula to evaluate after the display formula. Because you are forcing this formula to evaluate after a formula that s in the second pass, there s no need to include WhilePrintingRecords in this formula.

Tip  

As you begin to add formulas that calculate and reset variables, you may find quite a few instances of things appearing in details and group header sections that show zeros or other unnecessary information. You can t delete the formulas from these sections, because then they won t evaluate properly. To hide them, just click Suppress on the Common tab of the Format Editor. You ll then see them on the Design tab, but not on the Preview tab or any other report output.

When Not to Use Variables

It s fairly common to learn how to use certain spiffy features of a tool, and then to use them to excess! Variables have that potential. Although they are fast and, if used judiciously, don t consume significant extra memory or resources, they can sometimes be overkill. If you find a use for variables, first look closely at your report to see whether an easier, quicker way exists to accomplish the same task.

Figure 5-6 is an example of a report that counts orders that exceed a $1,000 bonus level. The number of orders needs to be shown both at the group level and at the end of the report.

Figure 5-6: Over $1,000 bonus report

Using variables to accomplish this requires the creation of several formulas. Two variables are also required: one to accumulate the bonus order count for each group, and one to count for the whole report. Following are the formulas.

@Bonus Calc is placed in the details section and suppressed:

 WhilePrintingRecords; 
 NumberVar CountCustomer; 
 NumberVar CountReport; 
 If {Orders.Order Amount} > 1000 Then 
 (CountCustomer := CountCustomer + 1; 
 CountReport := CountReport + 1) 

@Show Group Bonus is placed in the group footer:

 WhilePrintingRecords; 
 NumberVar CountCustomer; 
 "This customer had " + ToText(CountCustomer,0) + " bonus orders." 

@Reset Group Bonus is placed in the group header and suppressed:

 WhilePrintingRecords; 
 NumberVar CountCustomer := 0; 

@Show Report Bonus is placed in the report footer:

 WhilePrintingRecords; 
 NumberVar CountReport; 
 "This report had " + ToText(CountReport,0) + " bonus orders." 

While this will work, there is a much simpler way to accomplish the same task with just one formula using no variables. Create a single formula, place it in the details section, and suppress it. It will simply consist of the following:

 If {Orders.Order Amount} > 1000 Then 1 

When you place this in the details section, it will return a number constant of 1 when an order exceeds $1,000. If an order is under $1,000, the number formula will return 0 (because the formula is numeric and there is no Else clause, it will return 0 if the If test fails). You then simply need to insert a group subtotal and a report grand total on the formula to calculate group and report totals.

The result: the same totals with much less effort. This simple technique of assigning a formula a value of 1 if a test is passed can become the cornerstone for a lot of statistics-type reports you may have to write.

You may also be able to save time by using running total fields instead of formulas with variables. The running total report earlier in the chapter that illustrates evaluation times is a perfect example. In this type of report, there s no need to create formulas to calculate the running total. Running total fields are covered later in this chapter.

Tip  

Many of the types of formulas illustrated in this chapter are included in the sample reports on this book s accompanying web site. Look at www.CrystalBook.com for FORMULAS.RPT and BONUS.RPT to see how these, and similar formulas, are implemented.