- Introduction
- Filtering the Data in Your Report
- Learning to Sort Records
- Working with the Sort Expert
- Creating Effective Summaries
- Troubleshooting
- Crystal Reports in the Real World—Nesting Formulas
This chapter is from the book
This chapter is from the book
This chapter is from the book
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.
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.
Create a new formula from the Field Explorer named Large Orders with the following code:
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.
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:
Under Reset select Group 1. Check your settings against Figure 3.10.
Now with the numerator and denominator values defined, simply create a new formula called Avg Large Deal Size with the following code:
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) .
WhileReadingRecords;
If {Orders.Order Amount} > 3000 Then
{Orders.Order Amount}
Else
0;
{@Large Orders}>0
)
Figure 3.9 This is the starting point for the new functionality.
)
Figure 3.10 Create running totals easily using the Running Total Expert.
Sum ({@Large Orders}, {Orders.Customer ID})/{#Large Order Count}
)
Figure 3.11 A report complete with complex formulas.