Crystal Reports in the Real World—Nesting 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. 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. [View full size image]
2.	Create a new formula 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. 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. [View full size image]
5.	Now with the numerator and denominator values defined, simply create a new formula with the following code: Sum ({@Large Orders}, {Orders.Customer ID})/{#RTotal0}
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 (see Figure 3.11). Figure 3.11. A report complete with complex formulas. [View full size image]
7.	Save the report as Chap3AverageLargeOrder.rpt.