- 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. 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 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. For Type Of Summary select Count; for Evaluate, select Formula and enter the following code:
Now with the numerator and denominator values defined, simply create a new formula 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 (see Figure 3.11).
Save the report as Chap3AverageLargeOrder.rpt.
WhileReadingRecords;
If {Orders.Order Amount} > 3000 Then
{Orders.Order Amount}
Else
0;
{@Large Orders}>0
Under Reset select Group 1. Check your settings against Figure 3.10.
)
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})/{#RTotal0}
)
Figure
3.11 A report complete with complex formulas.