Crystal Reports in the Real WorldNesting Formulas

It's common for some more complex formulas to be combined to provide specific insight into report data. For example, a user might need to have a report that lists all customers with their total sales, but also show the average value of sales over a given amount. As described previously, there are many ways that a report design expert can approach this; what follows is one method.

1.	Open the report Chap3RunningTotal.rpt, or use the report you just created in the last section. Insert a group on Customer ID. Select the running total field, right-click it, and choose Edit Running Total. Under the Reset section, choose On Change Of Group. Now the report is ready for the new functionality and should look like Figure 3.9. Figure 3.9. This is the starting point for the new functionality.
2.	Create a new formula from the Field Explorer named Large Orders with the following code: WhileReadingRecords; If {Orders.Order Amount} > 3000 Then {Orders.Order Amount} Else 0;
3.	Add this formula to the report. Right-click on the new formula field and select Insert, Summary and for the section Summary Location change this value to your Group 1 field. This creates the numerator for your average.
4.	Next, to determine the value for the denominator, right-click the Large Orders formula and choose Insert, Running Total. Name the running total Large Order Count; for Type Of Summary select Count; for Evaluate, select Formula and enter the following code: {@Large Orders}>0 Under Reset select Group 1. Check your settings against Figure 3.10. Figure 3.10. Create running totals easily using the Running Total Expert.
5.	Now with the numerator and denominator values defined, simply create a new formula called Avg Large Deal Size with the following code: Sum ({@Large Orders}, {Orders.Customer ID})/{#Large Order Count}
6.	Insert this new formula onto the Group Footer and the report now has a summary value showing the average of all orders greater than $3,000 for each customer (see Figure 3.11). Figure 3.11. A report complete with complex formulas.